Postgres: лучший способ избежать перекрытия диапазонов с другими условиями

Question

Упрощенная таблица соединений MN

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?? */
 )

Я хочу очевидное правило исключения: если fkeyполя такие же, совпадений по датам нет. (Если ключи разные, исключения нет.)

Пока что моя лучшая идея - добавить дополнительный модуль куба и создать многоколоночный индекс по всем трем полям. Но хотя он выглядит как трехмерный куб, в двух из них ограничение перекрытия будет вырожденным. Тогда ЧТО ЗДЕСЬ - это

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

Оптимально ли это решение с использованием дополнительного модуля для относительно распространенного варианта использования?

Answer 1

Взгляни на:

https://www.postgresql.org/docs/12/rangetypes.html

на примере btree_gist.

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

где вы бы заменили свои значения FK, так что:

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

предполагая отдельные даты и включительно верхнюю дату.