This email was sent to docs, but I think it is a hackers issue. The person is asking why exclusion constraints aren't marked as UNIQUE indexes that can be used for referential integrity. I think the reason is that non-equality exclusion constraints, like preventing overlap, but don't uniquely identify a specific value, and I don't think we want to auto-UNIQUE just for equality exclusion constraints.
--------------------------------------------------------------------------- On Tue, Jul 10, 2018 at 09:34:36AM +0000, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/10/static/sql-createtable.html > Description: > > Hi. > > https://www.postgresql.org/docs/current/static/sql-createtable.html#sql-createtable-exclude > If all of the specified operators test for equality, this is equivalent to a > UNIQUE constraint > > Exclusion constraints are implemented using an index > > > ALTER TABLE person > add constraint person_udx_person_id2 > EXCLUDE USING gist ( > person_id WITH = > ) > ; > > tucha=> ALTER TABLE "person_x_person" ADD CONSTRAINT > "person_x_person_fk_parent_person_id" > tucha-> FOREIGN KEY ("parent_person_id") > tucha-> REFERENCES "person" ("person_id") > tucha-> ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE; > ERROR: there is no unique constraint matching given keys for referenced > table "person" > > because gist does not support unique indexes, I try with 'btree' > > > ALTER TABLE person > add constraint person_udx_person_id2 > EXCLUDE USING btree ( > person_id WITH = > ) > ; > > \d person > ... > "person_udx_person_id2" EXCLUDE USING btree (person_id WITH =) > > tucha=> ALTER TABLE "person_x_person" ADD CONSTRAINT > "person_x_person_fk_parent_person_id" > tucha-> FOREIGN KEY ("parent_person_id") > tucha-> REFERENCES "person" ("person_id") > tucha-> ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE; > ERROR: there is no unique constraint matching given keys for referenced > table "person" > > Why postgres does not add unique flag. Despite on: "this is equivalent to a > UNIQUE constraint" > I thought it should be: > "person_udx_person_id2" UNIQUE EXCLUDE USING btree (person_id WITH =) > > PS. > > For example, you can specify a constraint that no two rows in the table > contain overlapping circles (see Section 8.8) by using the && operator. > > Also I expect that this: > ALTER TABLE person > add constraint person_udx_person_id > EXCLUDE USING gist ( > person_id WITH =, > tstzrange(valid_from, valid_till, '[)' ) WITH && > ) > > also should raise UNIQUE flag for exclusion thus we can use it in FK -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
