Re: Need help writing exclusion constraint

On Sat, 2011-01-15 at 19:17 +0000, Matthew Wilson wrote:
> create table event(
>
> destination_id integer not null references destination
> (destination_id),
>
> starts timestamp,
> ends timestamp
> );
>
> I want to make sure that no two rows **with the same destination_id**
> overlap in time.

First, you need to have some notion of "overlaps", so you need to
combine the "starts" and "ends" into a single value. I recommend trying
the PERIOD datatype (as Andreas suggests). They don't have to be in the
same column necessarily (you could use a functional index that combines
the values), but typically it would be helpful anyway.

If you use the PERIOD datatype, the "overlaps" operator is "&&". So,
assuming that the combined start/end is called "during", the exclusion
constraint might look something like:

EXCLUDE USING gist (destination_id WITH =, during WITH &&)

You'll need to install the contrib module "btree_gist" first, so that
"=" is indexable over integers using GiST.

What's the above constraint says is: "rows R1 and R2 conflict if
R1.destination_id = R2.destination_id AND R1.during && R2.during", and
it will prevent R1 and R2 from both existing at the same time in your
table.

This method will be safe from race conditions.

Hope this helps. Also, for more detailed examples that happen to be very
similar to your problem, see:

http://thoughts.j-davis.com/2009/11/08/temporal-keys-part-2/
http://thoughts.j-davis.com/2010/09/25/exclusion-constraints-are-generalized-sql-unique/

Regards,
Jeff Davis