Re: Ranges for well-ordered types

On 6/10/06, Michael Glaesemann <grzm(at)seespotcode(dot)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