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 <bruce@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 +
