Ian Caulfield wrote:
On 6/10/06, *Michael Glaesemann* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
