Postgres: best way to avoid overlapping ranges with other conditions

Question

Simplified M-N join table

CREATE TABLE dummy (
   fkey1 int, /* omitting FK clause */
   fkey2 int,

    /* could also separate begin and end dates */
   effective_dates_of_assignment daterange,

   EXCLUDE /* WHAT GOES HERE?? */
 )

I want the obvious exclusion rule that if the fkey fields are the same, there is no overlap in the dates. (If the keys are different, there is no exclusion.)

My best idea so far is to add in the contributed cube module and create a gist multicolumn index on all three fields. But although it looks like a 3-D cube, the overlap constraint will be degenerate in two of them. Then the WHAT GOES HERE is

EXCLUDE USING gist (cube([fkey1, fkey2, lower(effective_dates_of_assignment)],
                         [fkey1, fkey2, upper(effective_dates_of_assignment)])
              WITH &&)

Is this solution, using an additional module, optimal for a relatively common use case?

Answer 1

Take a look at:

https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-INDEXING

using btree_gist example.

CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);

where you would substitute your FK values, so:

EXCLUDE USING GIST (fkey1 WITH =, fkey2 WITH =, daterange(date_start, date_end, '[]'::text) WITH &&)

assuming separate dates and inclusive upper date.