clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1228823 fiddles created (16673 in the last week).

CREATE TABLE tbl ( tbl_id serial PRIMARY KEY , tsr tsrange , CONSTRAINT tsr_no_overlap EXCLUDE USING gist (tsr WITH &&) , CONSTRAINT tsr_no_adjacent EXCLUDE USING gist (tsr WITH -|-) , CONSTRAINT tsr_enforce_bounds CHECK (lower_inc(tsr) AND NOT upper_inc(tsr)) ); INSERT INTO tbl(tsr) VALUES ('[2013-10-22 00:00, 2013-10-22 01:00)') , ('[2013-10-22 02:00, 2013-10-22 03:00)');
2 rows affected
 hidden batch(es)


-- Succeeds INSERT INTO tbl(tsr) VALUES ('[2013-10-22 06:00, 2013-10-22 07:00)');
1 rows affected
 hidden batch(es)


-- Conflict with tsr_no_overlap INSERT INTO tbl(tsr) VALUES ('[2013-10-22 00:00, 2013-10-22 04:00)');
ERROR: conflicting key value violates exclusion constraint "tsr_no_overlap" DETAIL: Key (tsr)=(["2013-10-22 00:00:00","2013-10-22 04:00:00")) conflicts with existing key (tsr)=(["2013-10-22 00:00:00","2013-10-22 01:00:00")).
 hidden batch(es)


-- Conflict with tsr_no_adjacent INSERT INTO tbl(tsr) VALUES ('[2013-10-22 03:00, 2013-10-22 04:00)');
ERROR: conflicting key value violates exclusion constraint "tsr_no_adjacent" DETAIL: Key (tsr)=(["2013-10-22 03:00:00","2013-10-22 04:00:00")) conflicts with existing key (tsr)=(["2013-10-22 02:00:00","2013-10-22 03:00:00")).
 hidden batch(es)


-- avoid conflicts INSERT INTO tbl(tsr) VALUES ('[2013-10-22 03:00, 2013-10-22 04:00)') ON CONFLICT DO NOTHING;
 hidden batch(es)


-- avoid only adjacent conflicts INSERT INTO tbl(tsr) VALUES ('[2013-10-22 02:30, 2013-10-22 04:00)') ON CONFLICT ON CONSTRAINT tsr_no_adjacent DO NOTHING;
ERROR: conflicting key value violates exclusion constraint "tsr_no_overlap" DETAIL: Key (tsr)=(["2013-10-22 02:30:00","2013-10-22 04:00:00")) conflicts with existing key (tsr)=(["2013-10-22 02:00:00","2013-10-22 03:00:00")).
 hidden batch(es)


-- exclusion constraints not supported as arbiters with ON CONFLICT DO UPDATE -- misleading error msg! INSERT INTO tbl(tsr) VALUES ('[2013-10-22 02:00, 2013-10-22 03:00)') ON CONFLICT (tsr) DO UPDATE SET tbl_id = tbl.tbl_id * -1;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
 hidden batch(es)


-- exclusion constraints not supported as arbiters with ON CONFLICT DO UPDATE INSERT INTO tbl(tsr) VALUES ('[2013-10-22 02:00, 2013-10-22 03:00)') ON CONFLICT ON CONSTRAINT tsr_no_overlap DO UPDATE SET tbl_id = tbl.tbl_id * -1
ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints
 hidden batch(es)


TABLE tbl;
tbl_id tsr
1 ["2013-10-22 00:00:00","2013-10-22 01:00:00")
2 ["2013-10-22 02:00:00","2013-10-22 03:00:00")
3 ["2013-10-22 06:00:00","2013-10-22 07:00:00")
 hidden batch(es)