Hmm, your using ColdFusion, so that goes through the ODBC driver, which
picks up the 'primary key' by looking for an index named 'foo_pkey',
I think. Ah, here it is:
in interfaces/odbc/info.c:
sprintf(tables_query, "select ta.attname, ia.attnum" " from pg_attribute ta, pg_attribute ia, pg_class c, pg_index i"
" where c.relname = '%s_pkey'" " AND c.oid = i.indexrelid" " AND ia.attrelid = i.indexrelid" " AND ta.attrelid
=i.indrelid" " AND ta.attnum = i.indkey[ia.attnum-1]" " order by ia.attnum", pktab);
So, don't name the primary key constraint, or name it 'something_pkey'
and you should be fine. Something's falling back to trying to use
oid if it can't find a primary key: I'm note sure if that's inside the
ODBC driver, or in ColdFusion.
Hmm, seems we have other Access specific hacks in the ODBC driver:
/** I have to hide the table owner from Access, otherwise it* insists on referring to the table as 'owner.table'.
(this*is valid according to the ODBC SQL grammar, but Postgres* won't support it.)**
set_tuplefield_string(&row->tuple[1],table_owner);*/
I bet PgAdmin would like to have that info.
Ross
On Tue, Jun 19, 2001 at 06:11:12PM -0400, P. Dwayne Miller wrote:
> My bad on the syntax of all three. I used your syntax (which is what I had originally used) and
> got the same results with the \d command that you show.
>
> I'm only using Cold Fusion to read data from the resulting table, not create the table... and I
> still get an error when I have created the primary key using the table constraint syntax. Cold
> Fusion is reporting that the primary key has been defined for the column oid. Using the correct
> syntax with the first two CREATE TABLE statements, Cold Fusion reports the primary key field as
> msgid.
>
> Thanks for your reply,
> Dwayne
>