Here is the table
CREATE TABLE date_ranges (
id SERIAL PRIMARY KEY,
user_id INTEGER,
start_at DATE,
end_at DATE,
is_exception BOOLEAN
);
ALTER TABLE date_ranges ADD CONSTRAINT date_ranges_bounds
EXCLUDE USING gist (user_id WITH =, daterange(start_at, end_at, '[]') WITH &&)
WHERE (NOT is_exception);
I have a table with working ranges and here is a flag is_exception
which indicates that this range is an exception from the regular range and I want to make a constraint that all regular ranges do not overlap each other and exception ranges belong to the regular range.
I tried to use <@
operator but have found that it is not commutative.
ALTER TABLE date_ranges ADD CONSTRAINT date_ranges_bounds
EXCLUDE USING gist (user_id WITH =, daterange(start_at, end_at, '[]') WITH <@)
WHERE (is_exception);
Is there any option to do this? Shall I create custom operator for this case?
**UPDATE**
Test cases:
user_id start_at end_at is_exception is_day_off
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
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"
Asked by Moldaone
(11 rep)
Mar 3, 2019, 01:56 PM
Last activity: Mar 3, 2019, 10:36 PM
Last activity: Mar 3, 2019, 10:36 PM