Postgres 11

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
 , 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

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

-- 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")).


-- 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")).


-- avoid conflicts
INSERT INTO tbl(tsr) VALUES ('[2013-10-22 03:00, 2013-10-22 04:00)')
ON CONFLICT DO NOTHING;
-- 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")).


-- 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


-- 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


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")

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")