public inbox for [email protected]  

help / color / mirror / Atom feed

From: PG Doc comments form <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Typo in doc or wrong EXCLUDE implementation
Date: Tue, 10 Jul 2018 09:34:36 +0000
Message-ID: <[email protected]> (raw)

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

view thread (12+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Typo in doc or wrong EXCLUDE implementation
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox