Exclusion constraint for unique constraint, is there a difference?

user_8888617 0
Viewed 1667

Assume the following table:

CREATE TABLE zoo (
    cage   INTEGER,
    animal TEXT,
);

Is there a real, effective difference between:

ALTER TABLE zoo ADD CONSTRAINT x EXCLUDE USING gist (cage WITH =, animal WITH =)

and:

CREATE UNIQUE INDEX ON zoo(cage, animal)

?

unique-constraint postgresql
1 Answers

I read this on the blog of the author of the exclude constraints:

http://thoughts.davisjeff.com/2010/09/25/exclusion-constraints-are-generalized-sql-unique/

DROP TABLE IF EXISTS a;
CREATE TABLE a(i int);
ALTER TABLE a ADD EXCLUDE (i WITH =);

That is identical to a UNIQUE constraint on a.i, except that it uses the Exclusion Constraints mechanism; it even uses a normal BTree to enforce it. The performance will be slightly worse because of some micro-optimizations for UNIQUE constraint, but only slightly, and the performance characteristics should be the same (it’s just as scalable). Most importantly, it behaves the same under high concurrency as a UNIQUE constraint, so you don’t have to worry about excessive locking. If one person inserts 5, that will prevent other transactions from inserting 5 concurrently, but will not interfere with a transaction inserting 6.

This more or less answers the question.

user_88886170
Related