Re: Ranges for well-ordered types

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

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
>
>