1

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"
2

The (not overlapping) constraint between "regular" ranges can be enforced with an EXCLUDE constraint, as you it defined in the question.

The (not overlapping) constraint between "exception" ranges can be enforced in the same manner and so can the constraint between "day off" ranges.

What is not trivial is the constraint between "regular" and "exception" ranges (and between "regular" and "day off" ranges):

For an exception there should be a regular range that the exception range is contained inside the regular one.

This is not commutative, so it cannot be done with EXCLUDE constraints. What you really need is a FOREIGN KEY constraint, a slightly more complicated one than a common foreign key constraint.

As an example, a foreign key constraint defined n table B:

CONSTRAINT a_b_fk
    FOREIGN KEY (aid)
    REFERENCES a (aid)

enforces that:

for any aid value in table B, there exists a row in table A with equal value in column aid

which is quite similar to what you have. The problematic difference is that foreign key constraints can use only the equality (=) and not any other operator. To solve this problem, we'll have to duplicate the columns that need to be checked with the different operator ("is contained by", <@).

As a result, we'll need to split the table into two, "regular" and "exception" ranges (lets forget the "day off" for a bit) and apply our solution:

For the "regular" ranges table, we'll only need an additional UNIQUE constraint, for the foreign key to be defined (and we can skip the is_exception and is_day_off columns):

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 &&
      )
);

For the "exception" ranges table, we'll need the FOREIGN KEY constraint and a CHECK constraint for the `contained" operator:

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,

  -- we don't need the UNIQUE, as the EXCLUDE covers it
  -- CONSTRAINT date_exception_ranges_uq
  --   UNIQUE (user_id, start_at, end_at),

  CONSTRAINT date_exception_ranges_bounds
    EXCLUDE USING gist
      ( user_id 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, '[]')
          )
);

For the "day off", since we identified that are similar to "exception" ranges, we can either add another table (with identical structure as the date_exception_ranges) or keep only that and add a boolean column to identify whether it is an "exception" or a "day off".

(The following assume that "exceptions" can overlap "day off" ranges. If they cannot, it's actually much simpler):

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, '[]')
          )
);

And finally, the above tested at dbfiddle.uk

  • Impressive! Thanks for your input. I got a question: What if I want to add the relation between date_ranges, let's say date_range_appointments in that case I should go with polymorphic association, I guess. Because an exclusion is a particular case when something is different. That's the reason why I tried to make it within single table. In my mind I see the solution in creating custom functions which query table for exclusion and hesitating will I suffer later with this table separation. – Moldaone Mar 3 at 22:21
  • You can produce the result (of your original, single table) as a view (with a UNION ALL between the two tables). What performance implications this will have depends on the queries. – ypercubeᵀᴹ Mar 3 at 22:23
  • I think I was not clear again. I was trying to say let's add another table with is "appointments" and it has to be linked to the range. According to the answer above since I have not 2 tables with actual ranges. If I want to create an appointment record I have to create either two columns for regular_range_id and for exception_range_id or make a polymorphic columns range_type & range_id. Isn't it an extra complexity comparing to a single table? – Moldaone Mar 3 at 22:38
  • Yes, off course. That would be more complex. Do you really need appointments for days off though? I don't see how that makes sense. – ypercubeᵀᴹ Mar 3 at 22:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.