By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tbl (
tbl_id serial PRIMARY KEY
, starts_at timestamp
, ends_at timestamp
, EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&) -- no overlap
);
INSERT INTO tbl (starts_at, ends_at) VALUES
('2014-11-01 10:00', '2014-11-01 12:00')
, ('2014-11-01 12:00', '2014-11-01 14:00')
;
CREATE TABLE
INSERT 0 2
-- Works
INSERT INTO tbl (starts_at, ends_at) VALUES
('2014-11-01 14:00', '2014-11-01 16:00');
TABLE tbl;
INSERT 0 1
| tbl_id | starts_at | ends_at |
|---|---|---|
| 1 | 2014-11-01 10:00:00 | 2014-11-01 12:00:00 |
| 2 | 2014-11-01 12:00:00 | 2014-11-01 14:00:00 |
| 3 | 2014-11-01 14:00:00 | 2014-11-01 16:00:00 |
SELECT 3
-- Fails
INSERT INTO tbl (starts_at, ends_at) VALUES
('2014-11-01 11:00', '2014-11-01 13:00');
ERROR: conflicting key value violates exclusion constraint "tbl_tsrange_excl" DETAIL: Key (tsrange(starts_at, ends_at))=(["2014-11-01 11:00:00","2014-11-01 13:00:00")) conflicts with existing key (tsrange(starts_at, ends_at))=(["2014-11-01 10:00:00","2014-11-01 12:00:00")).