Sample Header Ad - 728x90

Include constraint for daterange type

1 vote
1 answer
363 views
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