Re: Non-Overlaping date interval index

On Sat, Feb 18, 2006 at 10:03:11AM +0100, Pailloncy Jean-Gerard wrote:
> 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

Unfortunatly no. There have been discussions about extending unique
indexes to something other than just single values but no code has been
produced yet.

You can get fairly close with a trigger though (not syntactically
correct):

create function range_check() returns trigger as $$
if exists( select 1 from test
   where (new.start < start) <> (new.end > end) then
   raise error 'Conflict'
return NEW;
$$;

create trigger blah on insert or update to test do range_check();

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.