Issuing the following ( admittedly bogus ) statement against 7.1.1

CREATE TABLE dir_suppliers_var_prodtype (
  dir_suppliers_var_prodtype_id INTEGER ,
  dir_suppliers_var_id  integer DEFAULT 0 NOT NULL,
  prodtype_id  smallint DEFAULT 0 NOT NULL,
  PRIMARY KEY
(dir_suppliers_var_prodtype_id,dir_suppliers_var_prodtype_id)
);

gives the following , initially slightly cryptic response.

NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'dir_suppliers_var_prodtype_pkey' for table 'dir_suppliers_var_prodtype'

ERROR:  Cannot insert a duplicate key into unique index
pg_attribute_relid_attnam_index

This is obviously because of the broken primary key definition.
 
My question is, should this not raise a parser error ? It took me a
little while to actually spot the problem with the users statement.



-- 
Colin M Strickland  perl -e'print "\n",map{chr(ord()-3)}(reverse split 
             //,"\015%vhlwlqxpprF#ir#uhzrS#hkw#jqlvvhqudK%#\015\015nx".
"1rf1wilv1zzz22=swwk###369<#84<#:44#77.={di##339<#84<#:44#77.=ohw\015]".
"K9#4VE#/ORWVLUE#/whhuwV#dlurwflY#334#/wilV\015uhsrohyhG#ehZ#urlqhV");'

04 Jul 2001 14:28:46 +0100
Colin Strickland <cms@sift.co.uk>
CREATE TABLE .. PRIMARY KEY quirk
Colin Strickland <cms@sift.co.uk> writes:
>   PRIMARY KEY
> (dir_suppliers_var_prodtype_id,dir_suppliers_var_prodtype_id)
 
> My question is, should this not raise a parser error ?

Yes, it should.  SQL92 saith

         4) Each <column name> in the <unique column list> shall identify
            a column of T, and the same column shall not be identified more
            than once.

Looks like we neglect to make that check during initial processing of
the PRIMARY KEY clause.

			regards, tom lane

Wed, 04 Jul 2001 13:12:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: CREATE TABLE .. PRIMARY KEY quirk 