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