Creating unique constraints on OID

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