Re: Is this a bug?

I think this may have been discussed before but I found this a bit
surprising:

foo=# SELECT version();

version
---------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
20031022 (Red Hat Linux 3.3.2-1)
(1 row)

foo=# create table foo (id1 int, id2 int, id3 int);
CREATE TABLE
foo=# ALTER TABLE foo ADD unique (id1,id2);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index
"foo_id1_key" for table "foo"
ALTER TABLE
foo=# ALTER TABLE foo ADD unique (id1,id3);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index
"foo_id1_key" for table "foo"
ERROR: relation "foo_id1_key" already exists
foo=#

Now, I know I can specify a constraint name inside the alter command,
but I still expected this to work.

Thanks,

Matthew

"Matthew T. O'Connor" <matthew(at)zeut(dot)net> writes:
> Now, I know I can specify a constraint name inside the alter command,
> but I still expected this to work.

It does, in 8.0.

regression=# create table foo (id1 int, id2 int, id3 int);
CREATE TABLE
regression=# ALTER TABLE foo ADD unique (id1,id2);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "foo_id1_key" for table "foo"
ALTER TABLE
regression=# ALTER TABLE foo ADD unique (id1,id3);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "foo_id1_key1" for table "foo"
ALTER TABLE
regression=#

Of course there's no free lunch: it's significantly harder to guess what
the index name will be...

regards, tom lane

On Tue, Jan 25, 2005 at 12:43:16PM -0500, Matthew T. O'Connor wrote:

> foo=# ALTER TABLE foo ADD unique (id1,id3);
> NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index
> "foo_id1_key" for table "foo"
> ERROR: relation "foo_id1_key" already exists

8.0.0 handles this situation better:

test=> create table foo (id1 int, id2 int, id3 int);
CREATE TABLE
test=> ALTER TABLE foo ADD unique (id1,id2);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "foo_id1_key" for table "foo"
ALTER TABLE
test=> ALTER TABLE foo ADD unique (id1,id3);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "foo_id1_key1" for table "foo"
ALTER TABLE

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/