Re: unique index for periods - Mailing list pgsql-general

Gerhard Heift <ml-postgresql-20081012-3518@gheift.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