Re: Constraining overlapping date ranges

Filip,
The period type + exclusion constraint was exactly the avenue I was taking, and I was also perplexed that period was not defined as immutable. Your solution, to force period() to be immutable, seemed like a reasonable one (though it didn't work, see below).

I tried implementing this on my existing table:
ALTER TABLE tbl ADD EXCLUDE USING gist
(f_point(id) WITH ~=, f_period(start_date, stop_date) WITH &&);

and the index correctly identified all of the overlapping periods in my table by failing with details on the improper key. HOWEVER, after fixing all of the offending data, the index still failed to create:
ERROR: failed to re-find tuple within index "tbl_f_point_f_period_excl"
HINT: This may be because of a non-immutable index expression.

(What does this mean?) The index seems to work on an empty table (per your example), but not on my populated table, and the HINT seems to indicate that period() is not, in fact, immutable, which makes me nervous about using this solution. Hopefully someone knows what's going on here.

So I think my other options are to use period data columns (rather than start_date and end_date), then no coercion is needed in the EXCLUDE clause; try to CAST the date periods to boxes (as you suggested); use a rule, per Vincent's suggestion; or not check for overlap as it may be uncommon.

Also, I found Jeff Davis's summary of the exclusion constraint helpful in case anyone's interested:
http://thoughts.j-davis.com/2010/09/25/exclusion-constraints-are-generalized-sql-unique/

Thanks, Robert

PS. I don't think the f_point function is necessary. Something like:
... EXCLUDE USING gist (id WITH =, f_period(start_date, end_date) WITH &&)
seems equivalent to your suggestion:
... EXCLUDE USING gist (f_point(id) WITH ~=, f_period(start_date, end_date) WITH &&)

-----Original Message-----
From: Filip Rembiałkowski [mailto:filip(dot)rembialkowski(at)gmail(dot)com]
Sent: Wednesday, December 22, 2010 8:28 AM
To: McGehee, Robert
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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