Предотвращение смежных / перекрывающихся записей с помощью EXCLUDE в PostgreSQL

Я создаю базу данных, которая хранит произвольные диапазоны даты / времени в PostgreSQL 9.2.4. Я хочу наложить на эту базу данных ограничение, которое заставляет диапазоны даты / времени быть не перекрывающимися и не смежными (поскольку два смежных диапазона могут быть выражены как один непрерывный диапазон).

Для этого я использую EXCLUDE ограничение с индексом GiST. Вот ограничение, которое у меня есть на данный момент:

ADD CONSTRAINT overlap_exclude EXCLUDE USING GIST (
    box(
        point (
            extract(EPOCH FROM "from") - 1,
            extract(EPOCH FROM "from") - 1
        ),
        point (
            extract(EPOCH FROM "to"),
            extract(EPOCH FROM "to")
        )
    ) WITH &&
);

Колонны from а также to оба TIMESTAMP WITHOUT TIME ZONEи дата / время хранятся в UTC (я конвертирую в UTC, прежде чем вставлять данные в эти столбцы в моем приложении, и для моего часового пояса базы данных установлено значение "UTC" в postgresql.conf).

Однако я думаю, что проблема, с которой я столкнулся, заключается в том, что это ограничение делает (неверное) предположение, что нет приращений времени, меньших одной секунды.

Стоит отметить, что для конкретных данных, которые я храню, мне нужно только второе разрешение. Тем не менее, я чувствую, что мне, возможно, все еще придется иметь дело с этим, так как типы SQL timestamp а также timestamptz оба имеют более высокое разрешение, чем одна секунда.

У меня вопрос: есть ли проблема с простым допущением второго разрешения, так как это все, что нужно моему приложению (или хочет), или, если есть, как я могу изменить это ограничение, чтобы иметь дело с долями секунды в надежный способ?

Источник

3 ответа

Решение

Типы диапазонов состоят из нижней и верхней границ, которые могут быть включены или исключены. Типичный вариант использования (и по умолчанию для типов диапазона) состоит в том, чтобы включить нижнюю границу и исключить верхнюю границу.

Исключение перекрывающихся диапазонов кажется очевидным. В руководстве есть хороший пример кода

Кроме того, создайте другое исключающее ограничение, используя соседний оператор-|- также исключить смежные записи. Оба должны быть основаны на индексах GiST, так как GIN в настоящее время не поддерживается для этого.

Я бы навязал [) границы для всех записей. Вы можете сделать это с дополнительным CHECK ограничение с использованием функций диапазона:

CREATE TABLE tbl (
   tbl_id serial PRIMARY KEY
 , tsr tsrange
 , EXCLUDE USING gist (tsr WITH &&)               -- no overlapping
 , EXCLUDE USING gist (tsr WITH -|-)              -- no adjacent
 , CHECK (lower_inc(tsr) AND NOT upper_inc(tsr))  -- enforce [) bounds
);

SQL Fiddle.

Источник

Вы можете переписать исключение с типом диапазона, введенным в 9.2. А еще лучше, вы можете заменить два поля диапазоном. Смотрите "Ограничения на диапазоны" здесь, с примером, который в основном соответствует вашему варианту использования:

http://www.postgresql.org/docs/current/static/rangetypes.html

Источник

Однако я думаю, что проблема, с которой я столкнулся, заключается в том, что это ограничение делает (неверное) предположение, что нет приращений времени, меньших одной секунды.

Ты в порядке, подумай

select 
  extract ('epoch' from now())
  , extract ('epoch' from now()::timestamp(0))
Источник
Другие вопросы по тегам