Combine a PostgreSQL EXCLUDE range constraint with

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?

Answer 1

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:

http://www.postgresql.org/docs/current/static/btree-gist.html