Thread: exclude constraints with same name?

I found some surprising behavior with the new EXCLUDE constraint in 9.0.0- it seems that EXCLUDE constraint names have
tobe unique across tables: 

test=# BEGIN;
BEGIN
test=# CREATE TABLE a(a INTEGER);
CREATE TABLE
test=# CREATE TABLE b(b INTEGER);
CREATE TABLE
test=# ALTER TABLE a ADD CONSTRAINT testo1 CHECK(a=1);
ALTER TABLE
test=# ALTER TABLE b ADD CONSTRAINT testo1 CHECK(b=1);
ALTER TABLE
test=# ALTER TABLE a ADD CONSTRAINT testo2 EXCLUDE (a WITH =);
NOTICE:  ALTER TABLE / ADD EXCLUDE will create implicit index "testo2" for table "a"
ALTER TABLE
test=# ALTER TABLE b ADD CONSTRAINT testo2 EXCLUDE (b WITH =);
NOTICE:  ALTER TABLE / ADD EXCLUDE will create implicit index "testo2" for table "b"
ERROR:  relation "testo2" already exists
test=#

Also, the error message is odd and could be improved. The workaround is to use unique constraint names, but I would
liketo better understand why they need to be unique in the first place when other constraint names need not be. 

Cheers,
M

On Fri, 2010-09-24 at 19:05 -0400, A.M. wrote:
> I found some surprising behavior with the new EXCLUDE constraint in
> 9.0.0- it seems that EXCLUDE constraint names have to be unique across
> tables:
>

That's consistent with UNIQUE constraints. It has to do with whether the
constraint is enforced by an index -- UNIQUE and EXCLUDE are both
enforced by indexes, and CHECK is not.

postgres=# create table a(i int);
CREATE TABLE
postgres=# create table b(i int);
CREATE TABLE
postgres=# alter table a add constraint c1 unique(i);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "c1" for
table "a"
ALTER TABLE
postgres=# alter table b add constraint c1 unique(i);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "c1" for
table "b"
ERROR:  relation "c1" already exists

I can see how that would be a little confusing, however.

Regards,
    Jeff Davis