Postgres 13

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 schedule (
  event_id    integer NOT NULL  -- REFERENCES event(event_id) ON DELETE CASCADE ON UPDATE CASCADE
, t_range     tsrange NOT NULL
, PRIMARY KEY (event_id, t_range)
, CHECK (t_range <@ '[1996-01-01 00:00, 1996-01-09 00:00)') -- restrict to period
, EXCLUDE USING gist (event_id WITH =, t_range WITH &&)
);

INSERT INTO schedule VALUES
  (1, '[1996-01-01 13:00, 1996-01-01 17:30)')
, (1, '[1996-01-02 13:00, 1996-01-02 17:30)')
, (1, '[1996-01-05 14:00, 1996-01-05 17:00)')
, (2, '[1996-01-02 23:00, 1996-01-03 01:30)')  -- crossing day barrier
, (2, '[1996-01-07 23:00, 1996-01-08 01:30)')  -- corner case sun-mon
;
5 rows affected

SELECT EXTRACT(DOW FROM lower(t_range)) AS dow_from -- day of week for lower bound.
     , EXTRACT(DOW FROM upper(t_range)) AS dow_to   -- same for upper
     , lower(t_range)::time AS time_from            -- start time
     , upper(t_range)::time AS time_to              -- end time
FROM   schedule
ORDER  BY event_id, t_range;


dow_from
dow_to
time_from
time_to


1
1
13:00:00
17:30:00

2
2
13:00:00
17:30:00

5
5
14:00:00
17:00:00

2
3
23:00:00
01:30:00

0
1
23:00:00
01:30:00


-- overlap raises exception
INSERT INTO schedule VALUES
  (1, '[1996-01-01 17:00, 1996-01-01 18:30)'); 
ERROR:  conflicting key value violates exclusion constraint "schedule_event_id_t_range_excl"
DETAIL:  Key (event_id, t_range)=(1, ["1996-01-01 17:00:00","1996-01-01 18:30:00")) conflicts with existing key (event_id, t_range)=(1, ["1996-01-01 13:00:00","1996-01-01 17:30:00")).

dow_from	dow_to	time_from	time_to
1	1	13:00:00	17:30:00
2	2	13:00:00	17:30:00
5	5	14:00:00	17:00:00
2	3	23:00:00	01:30:00
0	1	23:00:00	01:30:00