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
Tom Lane wrote:
> "P. Dwayne Miller" <dmiller@espgroup.net> writes:
> > CREATE TABLE message
> > (
> > int4 msgid not null,
> > text msgtext,
> > CONSTRAINT cons_001_pk PRIMARY KEY on (msgid)
> > );
>
> > The first two actually create a PRIMARY KEY on msgid. The third seems
> > to have a PRIMARY KEY on 'oid', not 'msgid', though it does create a
> > unique index on 'msgid'.
>
> After fixing the several obvious syntax errors, it works fine for me:
>
> regression=# CREATE TABLE message
> regression-# (
> regression(# msgid int4 not null,
> regression(# msgtext text,
> regression(# CONSTRAINT cons_001_pk PRIMARY KEY (msgid)
> regression(# );
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'cons_001_pk' for table 'message'
> CREATE
> regression=# \d message
> Table "message"
> Attribute | Type | Modifier
> -----------+---------+----------
> msgid | integer | not null
> msgtext | text |
> Primary Key: cons_001_pk
>
> regression=#
>
> Is Cold Fusion perhaps doing strange things to the query behind your
> back? None of those CREATE TABLE commands are legal SQL according
> to my references.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster