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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
