PostgreSQLers,
I'm hoping for some help creating a constraint/key on a table such that there are no overlapping ranges of dates for
anyid.
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
