Thread: Non-Overlaping date interval index
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 Cordialement, Jean-Gérard Pailloncy
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.
Attachment
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 ===
On 2006-02-18, "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote: > am 18.02.2006, um 10:03:11 +0100 mailte Pailloncy Jean-Gerard folgendes: >> I wonder how to have a sort of "uniq" index on date interval, such that >> there is no date interval overlaping in the table. > > You can write a trigger to check the count of records which overlaps > (start,end): Doesn't work reliably due to the race condition, unless you lock the table against conflicting inserts. The problem of course is that the trigger doesn't see uncommitted rows from concurrent transactions. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
A. Kretschmer <andreas.kretschmer@schollglas.com> schrieb: > 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): Sorry, there are a problem: the trigger can't see uncommitted inserts from concurrent transactions. Thanks to AndrewSN for this hint. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°