What does exclusion constraint `EXCLUDE USING gist (c WITH &&)` mean?

Question

From PostgreSQL document

Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null. The syntax is:

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

I was wondering what EXCLUDE USING gist (c WITH &&) means? In particular, gist(), c WITH && and EXCLUDE USING.

Can it be rewritten in terms of check? Thanks.

Ahoy

slideshare.net/pgconf/not-just-unique-exclusion-constraints – Hodges

@a_horse_with_no_name I love the example of excluding on <> to ensure that all rows match. – Torietorii

Answer 1

Whereas a CHECK constraint evaluates an expression based on a single row of the table, an EXCLUDE constraint evaluates a comparison of two rows in the table. Think of it like a generalised UNIQUE constraint: instead of "no two rows can be equal", you can say things like "no two rows overlap", or even "no two rows can be different".

In order to achieve this without checking every possible combination of values, it needs an appropriate index structure which allows it to find possible violations when you insert or update a row. This is what the gist part of the declaration refers to: a particular type of index which can be used to speed up operations other than equality.

The remainder of the declaration is the constraint itself: c is the column being tested, and && is the operator which must not return true for any pair of rows. In this case, && is the "overlaps" operator as listed on the geometric operators manual page.

So put together, the constraint EXCLUDE USING gist (c WITH &&) translates to "no two values of c must overlap each other (more precisely, A.c && B.c must return false or null for all distinct rows A and B), and please use a gist index to monitor this constraint".

Torietorii

Appreciate if you could add scenarios where it makes most sense to use – Caxton

Found a gist showing an example with rooms in a hotel which may not booked in the same time frame: gist.github.com/fphilipe/0a2a3d50a9f3834683bf – Declarer

@MaulikModi See at the end for another hotel room example here: blog.crunchydata.com/blog/postgres-constraints-for-newbies – Epiglottis

@Torietorii how do you make a constraint that ensures "no two rows can be different"? ie. I want to exclude tsrange overlaps only when the user is different... – Alberto

@Alberto That sounds like a new question, rather than anything directly related to this answer, so should be posted on its own page. There is an example in the slides linked by a_horse_with_no_name which might be what you're looking for though. – Torietorii

@Torietorii I made a question that is more fleshed out and also maybe there is a better way to do it then using something with NOT EQUAL... https://mcmap.net/q/260892/-postgres-exclude-constraint-for-overlapping-timestamp-ranges-only-where-another-column-39-s-row-is-not-equal/14305096 – Alberto