Re: Non-Overlaping date interval index

am 18.02.2006, um 10:03:11 +0100 mailte Pailloncy Jean-Gerard folgendes:
> Hi,
>
> I wonder how to have a sort of "uniq" index on date interval, such that
> there is no date interval overlaping in the table.
>
> exemple:
> create table test (start timestamp, end timestamp);
> with the constraint: end > start

You can write a trigger to check the count of records which overlaps
(start,end):

Example:

test=# select * from foo;
id | s | e
----+---------------------+---------------------
1 | 2006-01-01 00:00:00 | 2006-01-02 00:00:00
2 | 2006-01-02 00:00:00 | 2006-01-03 00:00:00
3 | 2006-01-03 00:00:00 | 2006-01-04 00:00:00
(3 rows)

test=# select count(*) from foo where (s,e) overlaps ('2006/01/05'::date, '2006/01/06'::date);
count
-------
0
(1 row)

test=# select count(*) from foo where (s,e) overlaps ('2006/01/01'::date, '2006/01/06'::date);
count
-------
3
(1 row)

Perhaps there are other solutions...

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===