Re: Ranges for well-ordered types

Ian Caulfield wrote:
>
> On 6/10/06, *Michael Glaesemann* <grzm(at)seespotcode(dot)net
> <mailto: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 :)

Why not define a start_date and end_date to determine range, and then
use the date overlap functions in postgresql?

Joshua D Drake

>
> Ian
>
>