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