On Jun 11, 2006, at 2:34 , Michael Glaesemann wrote:
On Jun 11, 2006, at 0:54 , Ian Caulfield wrote:
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.
As Tom already pointed out, this method leads to problems with
btree indexes. I haven't heavily tested my own implementation
(below), but it only returns 0 for equality, which is what btree
expects. All other possible relationships between two ranges have a
well-defined result of -1 or 1. I believe this should be enough to
prevent any transitivity issues with btree.
Of course, this method doesn't provide the non-overlapping
constraint. That still needs to be handled by a constraint trigger.
Michael Glaesemann
grzm seespotcode net
---------------------------(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
