Re: Constraining overlapping date ranges

2010/12/21 McGehee, Robert <Robert(dot)McGehee(at)geodecapital(dot)com>:
> PostgreSQLers,
> I'm hoping for some help creating a constraint/key on a table such that there are no overlapping ranges of dates for any id.
>
> Specifically: Using PostgreSQL 9.0.1, I'm creating a name-value pair table as such this:
>
> CREATE TABLE tbl (id INTEGER, start_date DATE, stop_date DATE, value REAL);
>
> For a given id, I'd like to enforce that there is only one valid value on a given date. For instance, this would be acceptable:
>
> id      start_date      stop_date       value
> 2       2010-11-01      2010-12-01      3
> 2       2010-12-02      2010-12-15      4
> 3       2010-10-15      2010-12-15      -3
>
> But this would not: (notice start_date of line 2 is before stop_date of line 1).
> id      start_date      stop_date       value
> 2       2010-11-01      2010-12-01      3
> 2       2010-11-30      2010-12-15      4
> 3       2010-10-15      2010-12-15      -3
>
> I'd also appreciate it if anyone can provide any indexing hints on this table to optimize queries like:
> SELECT value FROM tbl WHERE id=2 and '2010-12-02' BETWEEN start_date AND stop_date;
>
> Thanks in advance, and sorry if I overlooked any obvious documentation!
>

No one has mentioned exclusionn constraints yet... :-)

I have combined it with period contrib (see
http://pgfoundry.org/projects/temporal/) to do what you want.
Note: you don't have to use this contrib; equivalently, you can CAST
date periods to boxes, just make sure the function is injective.

-- index requires immutable function, and for some unknown reason
(anybody?) point and period constructors are not immutable...
CREATE OR REPLACE FUNCTION f_point(integer) RETURNS point LANGUAGE sql AS
'SELECT point($1,$1)' IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION f_period(date,date) RETURNS period LANGUAGE sql AS
'SELECT period($1,$2)' IMMUTABLE STRICT;

CREATE TABLE tbl (
id integer NOT NULL,
start_date date NOT NULL,
end_date date,
CONSTRAINT tbl_exclude_overlaps EXCLUDE USING gist ( f_point(id)
WITH ~=, f_period(start_date,end_date) WITH && )
);

INSERT INTO tbl SELECT 1, '2010-01-01', '2010-12-31';
INSERT 0 1
INSERT INTO tbl SELECT 1, '2011-01-01', '2011-03-31';
INSERT 0 1
INSERT INTO tbl SELECT 2, '2010-01-01', '2010-12-21';
INSERT 0 1
INSERT INTO tbl SELECT 2, '2010-12-22', '2010-12-31';
INSERT 0 1
INSERT INTO tbl SELECT 2, '2010-12-25', '2010-12-26';
ERROR: conflicting key value violates exclusion constraint
"tbl_exclude_overlaps"
DETAIL: Key (f_point(id), f_period(start_date, end_date))=((2,2),
[2010-12-25 00:00:00+01, 2010-12-26 00:00:00+01)) conflicts with
existing key (f_point(id), f_period(start_date, end_date))=((2,2),
[2010-12-22 00:00:00+01, 2010-12-31 00:00:00+01)).

greets,
Filip