Include constraint for daterange type
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", &lt;@).

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


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

  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, '[]')
         &lt;@ 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 &amp;&amp;
      ),

  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, '[]')
         &lt;@ daterange(regular_start_at, regular_end_at, '[]')
          )
);


And finally, the above tested at dbfiddle.uk