StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>-- Implementation of a CONSTRAINT on non-overlapping datetime ranges -- , using the Postgres rulesystem. -- This mechanism should work for 8.4, without needing triggers.(tested on 9.0) -- We need a shadow-table for the rangesonly to avoid recursion in the rulesystem. -- This shadow table has a canary variable with a CONSTRAINT (value=0) on it -- , and on changes to the basetable (that overlap with an existing interval) -- an attempt is made to modify this variable. (which of course fails) -- CREATE SCHEMA tmp; DROP table tmp.dates_shadow CASCADE; CREATE table tmp.dates_shadow ( time_begin timestamp with time zone , time_end timestamp with time zone , overlap_canary INTEGER NOT NULL DEFAULT '0' CHECK (overlap_canary=0) ); ALTER table tmp.dates_shadow ADD PRIMARY KEY (time_begin,time_end) ; DROP table tmp.dates CASCADE; CREATE table tmp.dates ( time_begin timestamp with time zone , time_end timestamp with time zone , payload varchar ); ALTER table tmp.dates ADD PRIMARY KEY (time_begin,time_end) ; CREATE RULE dates_i AS ON INSERT TO tmp.dates DO ALSO ( -- verify shadow UPDATE tmp.dates_shadow ds SET overlap_canary= 1 WHERE (ds.time_begin, ds.time_end) OVERLAPS ( NEW.time_begin, NEW.time_end) ; -- insert shadow INSERT INTO tmp.dates_shadow (time_begin,time_end) VALUES (NEW.time_begin, NEW.time_end) ; ); CREATE RULE dates_d AS ON DELETE TO tmp.dates DO ALSO ( DELETE FROM tmp.dates_shadow ds WHERE ds.time_begin = OLD.time_begin AND ds.time_end = OLD.time_end ; ); CREATE RULE dates_u AS ON UPDATE TO tmp.dates WHERE NEW.time_begin <> OLD.time_begin AND NEW.time_end <> OLD.time_end DO ALSO ( -- delete shadow DELETE FROM tmp.dates_shadow ds WHERE ds.time_begin = OLD.time_begin AND ds.time_end = OLD.time_end ; -- verify shadow UPDATE tmp.dates_shadow ds SET overlap_canary= 1 WHERE (ds.time_begin, ds.time_end) OVERLAPS ( NEW.time_begin, NEW.time_end) ; -- insert shadow INSERT INTO tmp.dates_shadow (time_begin,time_end) VALUES (NEW.time_begin, NEW.time_end) ; ); INSERT INTO tmp.dates(time_begin,time_end) VALUES ('2011-09-01', '2011-09-10') , ('2011-09-10', '2011-09-20') , ('2011-09-20', '2011-09-30') ; SELECT * FROM tmp.dates; EXPLAIN ANALYZE INSERT INTO tmp.dates(time_begin,time_end) VALUES ('2011-09-30', '2011-10-04') ; INSERT INTO tmp.dates(time_begin,time_end) VALUES ('2011-09-02', '2011-09-04') ; SELECT * FROM tmp.dates; SELECT * FROM tmp.dates_shadow; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload