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@xxxxxxxxxxxxxxxx>: > 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@xxxxxxxxx] > Sent: Wednesday, December 22, 2010 8:28 AM > To: McGehee, Robert > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: Constraining overlapping date ranges > > 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
