Обсуждение: CREATE TABLE .. PRIMARY KEY quirk

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
0NOT 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");'

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
shallnot be identified more           than once.
 

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