Re: Constraining overlapping date ranges

Le mardi 21 décembre 2010 à 10:49 -0500, McGehee, Robert a écrit :
> 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
>

You could use a rule, as explained here:

http://www.postgresql.org/docs/8.4/static/rules-update.html

In your case, something like :

create table bad (like tbl);

CREATE RULE no_overlap AS ON INSERT to tbl WHERE EXISTS (SELECT 1 from
tbl t1 WHERE t1.start_date between NEW.start_date and NEW.stop_date or
t1.stop_date between NEW.start_date and NEW.stop_date AND t1.id=NEW.id)
DO INSTEAD INSERT INTO bad VALUES
(NEW.id,NEW.start_date,NEW.stop_date,NEW.value);

Then have your app check if the new record went into bad, for instance.

--
Vincent Veyron
http://marica.fr/
Progiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique