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