Postgres Pro
Downloads
Home   >   mailing lists

Thread: Creating unique constraints on OID

Creating unique constraints on OID

From
Peter Eisentraut
Date:
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

Re: Creating unique constraints on OID

From
Tom Lane
Date:
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

Re: Creating unique constraints on OID

From
"Christopher Kings-Lynne"
Date:
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

Re: Creating unique constraints on OID

From
Tom Lane
Date:
"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