Re: Constraining overlapping date ranges

[Filip RembiaÅkowski <filip.rembialkowski@xxxxxxxxx>]

2010/12/21 McGehee, Robert <Robert.McGehee@xxxxxxxxxxxxxxxx>:
> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general