On 6/10/06, Michael Glaesemann <grzm@seespotcode.net> wrote:Returning to my original example, with a "date_range" type, the table
could be defined as:
create table teachers__schools_2
(
teacher text not null
, school text not null
, period date_range not null
, primary key (teacher, school, period)
);
The original check constraint is handled by the date_range type and
the two unique constraints are replaced by a single primary key
constraint. Constraints for overlapping and continuity are still
handled using constraint triggers, but are easier to implement using
functions available to compare ranges rather than handling beginning
and end points individually.
I've done similar date range things by creating a composite type consisting of the lower and upper bounds, and then implementing a btree opclass where the comparator returns 0 if two ranges overlap - this allows a current btree index to enforce non-overlapping ranges, and allows indexed lookup of which range contains a particular value.
Not sure whether this covers your scenario, but it works fairly well for me :)
Ian