Re: unique index for periods

Gerhard Heift <ml-postgresql-20081012-3518(at)gheift(dot)de> writes:
> I try to create an unique index for a (time)period, and my goal is to
> prevent two overlapping periods in a row.

> To use the btree index I added a compare function:

> return
> CASE
> WHEN $1.next <= $2.first THEN -1
> WHEN $2.next <= $1.first THEN 1
> ELSE 0
> END;

This does not work as a btree compare function, because it fails to
satisfy the basic requirements of a total order. In particular it
doesn't satisfy the transitive law that A=B and B=C must imply A=C.

I don't believe it is possible to use a btree index for this purpose,
because there just isn't a way to express "overlaps" as a total order.
It'd be really nice to have uniqueness support in gist indexes someday
...

regards, tom lane