From: | PG Doc comments form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-docs(at)lists(dot)postgresql(dot)org |
Cc: | kes-kes(at)yandex(dot)ru |
Subject: | Typo in doc or wrong EXCLUDE implementation |
Date: | 2018-07-10 09:34:36 |
Message-ID: | 153121527691.1408.5686988620817799073@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | PG Doc comments form | 2018-07-10 15:57:52 | Multivariate statistics |
Previous Message | David G. Johnston | 2018-07-07 15:26:49 | Re: \i and \ir separated by \if now... |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrey V. Lepikhov | 2018-07-10 09:41:30 | Re: [PATCH] Timestamp for a XLOG_BACKUP_END WAL-record |
Previous Message | Peter Eisentraut | 2018-07-10 09:33:20 | Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation() |