Как обеспечить записи с неперекрывающимися временными диапазонами?

Question

Мне нужно убедиться, что моя база данных содержит только записи, в которых два или более столбца уникальны. Этого легко добиться с помощьюUNIQUE ограничение на эти столбцы.

В моем случае мне нужно запретить дублирование только для перекрывающихся временных диапазонов. В таблице естьvalid_from а также valid_toстолбцы. В некоторых случаях может сначала потребоваться срок действия активной записи до, установивvalid_to = now, а затем вставка новой записи, настроенной на valid_from = now а также valid_to = infinity.

Кажется, я могу без проблем истечь предыдущую запись, используя UPDATE, но вставка новой записи кажется проблематичной, поскольку мои базовые столбцы в настоящее время UNIQUE, и поэтому не может быть добавлен снова.

Я думал добавить valid_from а также valid_to как часть UNIQUEограничение, но это только сделало бы ограничение более свободным и позволило бы существовать дубликатам и перекрывающимся временным диапазонам.

Как мне сделать ограничение, чтобы гарантировать, что дубликаты не существуют с перекрытием valid_from а также valid_to tsrange?

Я, кажется, ищу EXCLUDE USING GIST, но, похоже, он не поддерживает несколько столбцов? Мне кажется, это не работает:

ALTER TABLE registration 
DROP Constraint IF EXISTS registration_{string.Join('_', listOfAttributes)}_key, 
ADD Constraint registration_{string.Join('_', listOfAttributes)}_key EXCLUDE USING GIST({string.Join(',', listOfAttributes)} WITH =, valid WITH &&);

Answer 1

Вы были на правильном пути. Но синтаксис для ограничений исключения немного отличается.

В зависимости от нераскрытого определения таблицы вам может потребоваться установить расширение (дополнительный модуль)btree_gistпервый. Один раз на db. Это необходимо для моего примера, поскольку требуемый класс оператора не установлен для типаinteger по умолчанию:

CREATE EXTENSION btree_gist;

Видеть:

• Ошибка PostgreSQL EXCLUDE USING: целочисленный тип данных не имеет класса оператора по умолчанию
• Как использовать (установить) dblink в PostgreSQL?

Потом:

CREATE TABLE registration  (
  tbl_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
, col_a  integer NOT NULL
, col_b  integer NOT NULL
, valid_from timestamp
, valid_to   timestamp
, CONSTRAINT no_overlap
    EXCLUDE USING gist (col_a with =, col_b with =, tsrange(valid_from, valid_to) WITH &&)
);

Каждый столбец должен быть указан с соответствующим оператором.

И вам нужен тип диапазона. Вы упоминаете отдельные столбцыvalid_from а также valid_to. И вы также упоминаетеtsrange а также validв неудачной команде. Это сбивает с толку. Если предположить, что дваtimestamp столбцы, индекс выражения с выражением tsrange(valid_from, valid_to) сделает это.

Связанные с:

• Выполните запрос часов работы в PostgreSQL
• Неперекрывающиеся непрерывные диапазоны временных меток (tstzrange) для часов работы
• Запрос Postgresql 9.4 становится все медленнее при присоединении к TSTZRANGE с помощью &&
• Сохранить день недели и время?

Обычно timestamptz (tstzrange) следует выбирать вместо timestamp (tsrange). Видеть:

• Полное игнорирование часовых поясов в Rails и PostgreSQL

---

Возможно, лучший дизайн - это отношение "один ко многим" между вашимиregistration таблица и записи 1-N в новом registration_rangeТаблица. И некоторая логика для определения действующей в данный момент записи (для любого заданного момента времени). Зависит от нераскрытой информации.