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