Combine a PostgreSQL EXCLUDE range constraint with a UNIQUE constraint

Question

In PostgreSQL, how do you combine an exclusion constraint on a range column with a unique constraint on other, scalar columns. Or to put it another way, how do I ensure that the range overlap check is only done in combination with a unique check on some other columns?

For example, say I have:

CREATE TABLE reservation (
    restaurant_id int,
    time_range tsrange
);

I want to make sure that for each restaurant_id, there are no overlapping time_ranges.

I know that I can create an exclusive range constraint like this:

CREATE TABLE reservation (
    restaurant_id int,
    time_range tsrange EXCLUDE USING gist (time_range WITH &&)
);

But how do I make sure that the time_range check is scoped by restaurant_id?

Here is a discussion of exactly this topic: thoughts.davisjeff.com/2010/09/25/…. — Gordon Linoff
– Gordon Linoff, Commented Nov 1, 2015 at 16:02

user330315 · Accepted Answer · 2015-11-01 16:02:56Z

11

CREATE TABLE reservation 
(
    restaurant_id int,
    time_range tsrange,
    EXCLUDE USING gist (restaurant_id with =, time_range WITH &&)
);

Note that you need to install the extension btree_gist because the GIST index does not have an equality operator by default:

Collectives™ on Stack Overflow

Combine a PostgreSQL EXCLUDE range constraint with a UNIQUE constraint

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related