Kevin McCarthy (kemcca...@gmail.com)

I have a table with a column that will join with one of several tables based
on the value of another column. I'm not sure if this column can be set as a
foreign key to any of these tables, may be simply bad design on my part. The
item_type_id column of a record of 'A' will reference one of the 'type'
table records depending upon what the value of the record's item_type column
is.

Is there a way to set the item_type_id column in Table A as a foreign key to
the other tables depending? Or would there be a better way to design this
schema?

TIA

CREATE TABLE ABC(
id SERIAL PRIMARY KEY,
item_type character varying(10) NOT NULL,
item_type_id INTEGER
);

CREATE TABLE typeA(
  typeA_id SERIAL PRIMARY KEY
);

CREATE TABLE typeB(
  typeB_id SERIAL PRIMARY KEY
);

CREATE TABLE typeC(
  typeC_id SERIAL PRIMARY KEY
);

I have a table with a column that will join with one of several tables based
on the value of another column. I'm not sure if this column can be set as a
foreign key to any of these tables, may be simply bad design on my part. The
item_type_id column of a record of 'A' will reference one of the 'type'
table records depending upon what the value of the record's item_type column
is.

Is there a way to set the item_type_id column in Table A as a foreign key to
the other tables depending? Or would there be a better way to design this
schema?

TIA

CREATE TABLE ABC(
id SERIAL PRIMARY KEY,
item_type character varying(10) NOT NULL,
item_type_id INTEGER
);

CREATE TABLE typeA(
  typeA_id SERIAL PRIMARY KEY
);

CREATE TABLE typeB(
  typeB_id SERIAL PRIMARY KEY
);

CREATE TABLE typeC(
  typeC_id SERIAL PRIMARY KEY
);

Thanks, this is the case.

As for changing it, docs seem to suggest that encoding can be set only upon
database creation. The database which we need to change is running in
production although with fairly low traffic at the present time. Would the
best suggestion be to dump the current database and re-import? And if so,
any hint on how to indicate the encoding upon creation? Docs probably state
how but just thought I'd ask.

Thanks again.

I'm running into a problem that, from my investigative work online, seems to
be more common than I'd suspected. We are hosting a site using Apache and
PHP5 that allows uploading of textual content into field tables via HTML
forms processed by code that inserts the text into text fields in various
tables.

Often users will copy and paste text directly from MS Word docs into the
forms which will invariably contain Microsoft's proprietary formatting of
characters such as 'smart' quotes, trademark, copyright symbols, accent
grave, etc. We've set the HTML pages as UTF-8 and the database connection to
UTF-8. However when our calls to import the data that includes any of these
characters into the database, the queries fail complaining that e.g.
"[nativecode=ERROR:  character 0xe28093 of encoding "UTF8" has no equivalent
in "LATIN9"]"

We've tried on the PHP end to translate various ASCII characters from
literal values to specified replacements but have not been able to catch
these anomalies. Any suggestions, recommendations, experiences to relate?

TIA