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. 2335719 fiddles created (27457 in the last week).

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
 hidden batch(es)


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 && ) );
 hidden batch(es)


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, '[]') ) );
 hidden batch(es)


-- 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
 hidden batch(es)


INSERT INTO date_regular_ranges ( user_id, start_at, end_at) VALUES (1, '2019-03-01', '2019-03-05') ; -- is fine
1 rows affected
 hidden batch(es)


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.
 hidden batch(es)


-- 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"
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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)).
 hidden batch(es)


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)).
 hidden batch(es)


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).
 hidden batch(es)


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).
 hidden batch(es)


select * from date_regular_ranges ;
id user_id start_at end_at
1 1 2019-03-01 2019-03-05
 hidden batch(es)


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
 hidden batch(es)