Обсуждение: Creating unique constraints on OID

Shouldn't this work?

create table test ( a int, unique (oid) );
ERROR:  CREATE TABLE: column "oid" named in key does not exist

Because this works:

create table test ( a int );
CREATE

alter table test add unique (oid);
NOTICE:  ALTER TABLE/UNIQUE will create implicit index 'test_oid_key' for table 'test'
CREATE

And shouldn't the last one say "ALTER"?

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Peter Eisentraut <peter_e@gmx.net> writes:
> Shouldn't this work?
> create table test ( a int, unique (oid) );
> ERROR:  CREATE TABLE: column "oid" named in key does not exist

Now it does.

regression=# create table test ( a int, unique (oid) );
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_oid_key' for table 'test'
CREATE
regression=# drop table test;
DROP
regression=# create table test ( a int, unique (oid) ) without oids;
ERROR:  CREATE TABLE: column "oid" named in key does not exist
regression=# create table test ( a int ) without oids;
CREATE
regression=# alter table test add unique (oid);
ERROR:  ALTER TABLE: column "oid" named in key does not exist
regression=# drop table test;
DROP
regression=# create table test ( a int );
CREATE
regression=# alter table test add unique (oid);
NOTICE:  ALTER TABLE/UNIQUE will create implicit index 'test_oid_key' for table 'test'
CREATE
regression=#

> And shouldn't the last one say "ALTER"?

The reason that happens is that parser/analyze.c transforms the command
into an ALTER TABLE step that adds a constraint (a no-op in this case)
plus a CREATE INDEX step.  The commandTag emitted by the last step is
what psql shows.  This could possibly be fixed, but it seems not worth
the trouble.
        regards, tom lane

So the result of all this is that the behaviour of my ADD UNIQUE code is
correct in this case?

> Peter Eisentraut <peter_e@gmx.net> writes:
> > Shouldn't this work?
> > create table test ( a int, unique (oid) );
> > ERROR:  CREATE TABLE: column "oid" named in key does not exist
>
> Now it does.

In 7.2 you mean?  Or did you just fix it then?

> > And shouldn't the last one say "ALTER"?
>
> The reason that happens is that parser/analyze.c transforms the command
> into an ALTER TABLE step that adds a constraint (a no-op in this case)
> plus a CREATE INDEX step.  The commandTag emitted by the last step is
> what psql shows.  This could possibly be fixed, but it seems not worth
> the trouble.

If it were to be changed - I really wouldn't know where to do that...

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> So the result of all this is that the behaviour of my ADD UNIQUE code is
> correct in this case?

The AlterTable code wasn't broken; the error was in parser/analyze.c,
which was prematurely rejecting the command.

>> Peter Eisentraut <peter_e@gmx.net> writes:
> Shouldn't this work?
> create table test ( a int, unique (oid) );
> ERROR:  CREATE TABLE: column "oid" named in key does not exist
>> 
>> Now it does.

> In 7.2 you mean?  Or did you just fix it then?

I just fixed it moments before sending that message.
        regards, tom lane