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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
