Combine PostgreSQL EXCLUDE Range Constraint with UNIQUE Constraint

Question

In PostgreSQL, how do you combine an exception constraint in a range column with a unique constraint on other, scalar columns. Or, to put it another way, how can I guarantee that a range overlap check is only performed in conjunction with a unique check on some other columns?

For example, let's 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_range s.

I know that I can create a limit on the width limit as follows:

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

But how can I make sure that the time_range check time_range limited to restaurant_id ?

Answer 1

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

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

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