exclude constraints with same name?

From: "A(dot)M(dot)" <agentm(at)themactionfaction(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: exclude constraints with same name?
Date: 2010-09-24 23:05:22
Message-ID: 513350EF-1BE8-4C8E-828A-375BF1DFFDF3@themactionfaction.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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:

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 like to better understand why they need to be unique in the first place when other constraint names need not be.

Cheers,
M

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2010-09-24 23:11:50 Re: pg 8.4 crashing.
Previous Message rey 2010-09-24 22:52:53 psql copy command - 1 char limitation on delimiter