Postgres date overlapping constraint

Question

I have a table like this:

date_start    date_end     account_id    product_id 2001-01-01    2001-01-31   1             1 2001-02-01    2001-02-20   1             1 2001-04-01    2001-05-20   1             1 

I want to disallow overlapping intervals a given (account_id, product_id)

EDIT: I found something:

CREATE TABLE test (                                                                                                     from_ts TIMESTAMPTZ,     to_ts TIMESTAMPTZ,     account_id INTEGER,     product_id INTEGER,     CHECK ( from_ts < to_ts ),     CONSTRAINT overlapping_times EXCLUDE USING GIST (         account_id WITH =,         product_id WITH =,         box(             point( extract(epoch FROM from_ts at time zone 'UTC'), extract(epoch FROM from_ts at time zone 'UTC') ),             point( extract(epoch FROM to_ts at time zone 'UTC') , extract(epoch FROM to_ts at time zone 'UTC') )         ) WITH &&     ) ); 

If you want to know more about this http://www.depesz.com/2010/01/03/waiting-for-8-5-exclusion-constraints/

My only problem is that it doesn't work with null values as a ending timestamp, I thought of replace it with infinite values but does not work as well.

Answer 1

Ok i ended up doing this :

CREATE TABLE test (     from_ts TIMESTAMPTZ,     to_ts TIMESTAMPTZ,     account_id INTEGER DEFAULT 1,     product_id INTEGER DEFAULT 1,     CHECK ( from_ts < to_ts ),     CONSTRAINT overlapping_times EXCLUDE USING GIST (         account_id WITH =,         product_id WITH =,         period(from_ts, CASE WHEN to_ts IS NULL THEN 'infinity' ELSE to_ts END) WITH &&     ) ); 

Works perfectly with infinity, transaction proof.

I just had to install temporal extension which is going to be native in postgres 9.2 and btree_gist available as an extension in 9.1 CREATE EXTENSION btree_gist;

nb : if you don't have null timestamp there is no need to use the temporal extension you could go with the box method as specified in my question.