Re: RFC: Temporal Extensions for PostgreSQL

On 2/17/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hannu Krosing <hannu@skype.net> writes:
> > How easy/hard would it be to create unique indexes on tinterval (unique
> > here meaning non-overlapping) ?
>
> "Overlapping" is not an equality relation (it fails the transitive law),
> so I'm not entirely sure what "unique" means in this context ... but I
> can promise you you can't make it work with btree.

Hmm, let's assume two time intervals:

A (with a0 as start and a1 as end times)
B (woth b0 as start and b1 as end times)

Now, we'd define operators as:

A is left of B when a0 < b0 AND a1 < b0
A is right of B when a0 > b1 AND a1 > b1

A is "equal" to B if (a0 >= b0 AND a0 <= b1) OR (a1 >= b0 AND a1 <=
b1) OR (a0 < b0 AND a1 > b1)
Actually equal doesn't mean equal here, rather it says "overlaps".

Now, assuming UNIQUE INDEX on such table, the order would be preserved
since no two intervals can overlap.  And no overlapping data could be inserted
without breaking "ovelapivity". And of course non-unique index would
produce garbage (since left of/right of wouldn't make any sense anymore).

Interestingly, such non-overlapping datatypes could also make sense for
network addresses (with netmasks).