Re: Constraining overlapping date ranges - Mailing list pgsql-general

that's really interesting; looks like a bug if point constructor is
really mutable? can you prepare a test case with non-overlapping
ranges which does not satisfy exclusion constraint?

regarding your last comment about unnecessary f_point function: I
tried "id WITH =" but postgres complained about lack of proper gist
opclass

2010/12/22, McGehee, Robert <Robert.McGehee@geodecapital.com>:
> 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.rembialkowski@gmail.com]
> Sent: Wednesday, December 22, 2010 8:28 AM
> To: McGehee, Robert
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Constraining overlapping date ranges
>
> 2010/12/21 McGehee, Robert <Robert.McGehee@geodecapital.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
>