80

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.

Improve this question
2

1 Answer 1

Reset to default
134

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".

Improve this answer
6
  • 1
    Appreciate if you could add scenarios where it makes most sense to use
    – Maulik Modi
    Commented May 29, 2021 at 17:45
  • 7
    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
    – Fabian
    Commented Aug 18, 2021 at 8:55
  • 1
    @MaulikModi See at the end for another hotel room example here: blog.crunchydata.com/blog/postgres-constraints-for-newbies
    – Blama
    Commented Feb 10, 2022 at 9:43
  • 1
    @IMSoP 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...
    – Zaffer
    Commented May 11, 2022 at 21:14
  • @Zaffer 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.
    – IMSoP
    Commented May 11, 2022 at 21:22