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?.
select version();


version


PostgreSQL 11.1 (Debian 11.1-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit


    CREATE TABLE date_regular_ranges (
      id SERIAL PRIMARY KEY,
      user_id INTEGER NOT NULL,
      start_at DATE NOT NULL,
      end_at DATE NOT NULL,
      
      CONSTRAINT date_regular_ranges_uq
        UNIQUE (user_id, start_at, end_at),
        
      CONSTRAINT date_regular_ranges_bounds
        EXCLUDE USING gist
          ( user_id WITH =, 
            daterange(start_at, end_at, '[]') WITH &&
          )
    );
    CREATE TABLE date_exception_ranges (
      id SERIAL PRIMARY KEY,
      user_id INTEGER NOT NULL,
      start_at DATE NOT NULL,
      end_at DATE NOT NULL,
      regular_start_at DATE NOT NULL,
      regular_end_at DATE NOT NULL,
      -- TRUE: is_exception, FALSE: is_day_off
      is_exception BOOLEAN NOT NULL,

      CONSTRAINT date_exception_ranges_bounds
        EXCLUDE USING gist
          ( user_id WITH =,
            (is_exception::int) WITH =,
            daterange(start_at, end_at, '[]') WITH &&
          ),

      CONSTRAINT date_regular_exception_ranges_fk
        FOREIGN KEY
          (user_id, regular_start_at, regular_end_at)
        REFERENCES date_regular_ranges
          (user_id, start_at, end_at),

      CONSTRAINT date_exception_ranges_ck
        CHECK ( daterange(start_at, end_at, '[]')
             <@ daterange(regular_start_at, regular_end_at, '[]')
              )
    );
-- 1           2019-03-01      2019-03-05  FALSE           FALSE       // is fine
-- 1           2019-03-01      2019-03-05  FALSE           FALSE       // FAIL. Range intersects with range above
INSERT INTO date_regular_ranges
  ( user_id, start_at, end_at)
VALUES
  (1,           '2019-03-01',      '2019-03-05') ;    -- is fine
1 rows affected

INSERT INTO date_regular_ranges
  ( user_id, start_at, end_at)
VALUES
  (1,           '2019-03-01',      '2019-03-05') ;   -- FAIL. Range intersects with range above
ERROR:  duplicate key value violates unique constraint "date_regular_ranges_uq"
DETAIL:  Key (user_id, start_at, end_at)=(1, 2019-03-01, 2019-03-05) already exists.


-- 1           2019-03-02      2019-03-03  TRUE            FALSE       // is fine since exception is inside "regular range above"
-- 1           2019-03-04      2019-03-04  TRUE            FALSE       // is fine since exception is inside "regular range above" & does not intersect another exception
-- 1           2019-03-03      2019-03-03  FALSE           TRUE        // is fine since day off is inside "regular range above"
-- 1           2019-03-05      2019-03-05  FALSE           TRUE        // is fine since day off is inside "regular range above" & does not intersect another day off

-- 1           2019-03-02      2019-03-03  TRUE            FALSE       // FAIL. Exception should NOT intersect with another one
-- 1           2019-03-03      2019-03-03  FALSE           TRUE        // FAIL. Day off should NOT intersect with another one

-- 1           2019-03-15      2019-03-15  TRUE            FALSE       // FAIL. Exception is NOT inside "regular range above"
-- 1           2019-03-10      2019-03-10  FALSE           TRUE        // FAIL. Day off is NOT inside "regular range above"
INSERT INTO date_exception_ranges
  ( user_id, start_at, end_at, regular_start_at, regular_end_at, is_exception)
VALUES
  (1,           '2019-03-02',      '2019-03-03',   '2019-03-01', '2019-03-05',  TRUE) ;       -- is fine since exception is inside "regular range above"
1 rows affected

INSERT INTO date_exception_ranges
  ( user_id, start_at, end_at, regular_start_at, regular_end_at, is_exception)
VALUES
  (1,           '2019-03-04',     '2019-03-04',   '2019-03-01', '2019-03-05',  TRUE) ;       -- is fine since exception is inside "regular range above" & does not intersect another exception
1 rows affected

INSERT INTO date_exception_ranges
  ( user_id, start_at, end_at, regular_start_at, regular_end_at, is_exception)
VALUES
  (1,           '2019-03-03',      '2019-03-03',  '2019-03-01', '2019-03-05',  FALSE) ;       -- is fine since day off is inside "regular range above"
1 rows affected

INSERT INTO date_exception_ranges
  ( user_id, start_at, end_at, regular_start_at, regular_end_at, is_exception)
VALUES
  (1,           '2019-03-05',     '2019-03-05',  '2019-03-01', '2019-03-05',  FALSE) ;       -- is fine since day off is inside "regular range above" & does not intersect another day off
1 rows affected

INSERT INTO date_exception_ranges
  ( user_id, start_at, end_at, regular_start_at, regular_end_at, is_exception)
VALUES
  (1,           '2019-03-02',      '2019-03-03',   '2019-03-01', '2019-03-05',   TRUE) ;       -- FAIL. Exception should NOT intersect with another one
ERROR:  conflicting key value violates exclusion constraint "date_exception_ranges_bounds"
DETAIL:  Key (user_id, (is_exception::integer), daterange(start_at, end_at, '[]'::text))=(1, 1, [2019-03-02,2019-03-04)) conflicts with existing key (user_id, (is_exception::integer), daterange(start_at, end_at, '[]'::text))=(1, 1, [2019-03-02,2019-03-04)).


INSERT INTO date_exception_ranges
  ( user_id, start_at, end_at, regular_start_at, regular_end_at, is_exception)
VALUES
  (1,           '2019-03-03',      '2019-03-03',  '2019-03-01', '2019-03-05',  FALSE) ;       -- FAIL. Day off should NOT intersect with another one
ERROR:  conflicting key value violates exclusion constraint "date_exception_ranges_bounds"
DETAIL:  Key (user_id, (is_exception::integer), daterange(start_at, end_at, '[]'::text))=(1, 0, [2019-03-03,2019-03-04)) conflicts with existing key (user_id, (is_exception::integer), daterange(start_at, end_at, '[]'::text))=(1, 0, [2019-03-03,2019-03-04)).


INSERT INTO date_exception_ranges
  ( user_id, start_at, end_at, regular_start_at, regular_end_at, is_exception)
VALUES
  (1,           '2019-03-15',      '2019-03-15',  '2019-03-01', '2019-03-05',  TRUE) ;       -- FAIL. Exception is NOT inside "regular range above"
ERROR:  new row for relation "date_exception_ranges" violates check constraint "date_exception_ranges_ck"
DETAIL:  Failing row contains (7, 1, 2019-03-15, 2019-03-15, 2019-03-01, 2019-03-05, t).


INSERT INTO date_exception_ranges
  ( user_id, start_at, end_at, regular_start_at, regular_end_at, is_exception)
VALUES
  (1,           '2019-03-10',      '2019-03-10',  '2019-03-01', '2019-03-05',  FALSE) ;       -- FAIL. Day off is NOT inside "regular range above"
ERROR:  new row for relation "date_exception_ranges" violates check constraint "date_exception_ranges_ck"
DETAIL:  Failing row contains (8, 1, 2019-03-10, 2019-03-10, 2019-03-01, 2019-03-05, f).


select * from date_regular_ranges ;


id
user_id
start_at
end_at


1
1
2019-03-01
2019-03-05


select *,
    not is_exception as is_day_off
from date_exception_ranges ;


id
user_id
start_at
end_at
regular_start_at
regular_end_at
is_exception
is_day_off


1
1
2019-03-02
2019-03-03
2019-03-01
2019-03-05
t
f

2
1
2019-03-04
2019-03-04
2019-03-01
2019-03-05
t
f

3
1
2019-03-03
2019-03-03
2019-03-01
2019-03-05
f
t

4
1
2019-03-05
2019-03-05
2019-03-01
2019-03-05
f
t
id	user_id	start_at	end_at	regular_start_at	regular_end_at	is_exception	is_day_off
1	1	2019-03-02	2019-03-03	2019-03-01	2019-03-05	t	f
2	1	2019-03-04	2019-03-04	2019-03-01	2019-03-05	t	f
3	1	2019-03-03	2019-03-03	2019-03-01	2019-03-05	f	t
4	1	2019-03-05	2019-03-05	2019-03-01	2019-03-05	f	t