在 PostgreSQL 中使用 EXCLUDE 防止相邻/重叠条目 [英] Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

问题描述

我正在创建一个数据库，用于在 PostgreSQL 9.2.4 中存储任意日期/时间范围.我想对这个数据库设置一个约束，强制日期/时间范围不重叠且不相邻(因为两个相邻的范围可以表示为一个连续的范围).

I am creating a database which stores arbitrary date/time ranges in PostgreSQL 9.2.4. I want to place a constraint on this database which forces the date/time ranges to be non-overlapping, and non-adjacent (since two adjacent ranges can be expressed as a single continuous range).

为此，我使用了带有 GiST 索引的 EXCLUDE 约束.这是我目前的约束:

To do this, I am using an EXCLUDE constraint with a GiST index. Here is the constraint I have currently:

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在我的应用程序中将数据插入到这些列中，并且我在 postgresql.conf 中将数据库的时区设置为UTC").

The columns from and to are both TIMESTAMP WITHOUT TIME ZONE, and are date/times stored in UTC (I convert to UTC before inserting data into these columns in my application, and I have my database's timezone set to "UTC" in postgresql.conf).

不过，我认为我可能遇到的问题是，此约束正在做出(不正确的)假设，即没有小于一秒的时间增量.

The problem I am thinking I might have, though, is that this constraint is making the (incorrect) assumption that there are no time increments smaller than one second.

值得注意的是，对于我存储的特定数据，我只需要第二个分辨率.但是，我觉得我可能仍然需要处理这个问题，因为 SQL 类型 timestamp 和 timestamptz 的分辨率都高于一秒.

It is worth noting that, for the particular data I am storing, I only need second resolution. However, I feel that I may still need to deal with this since the SQL types timestamp and timestamptz are both higher resolution than one second.

我的问题是:简单地假设第二个分辨率是否有任何问题，因为这就是我的所有应用程序需要(或想要的)，或者，如果有，我如何更改此约束以处理小数部分以稳健的方式排在第二位?

My question is either: is there any problem with simply assuming second resolution, since that's all my application needs (or wants), or, if there is, how can I alter this constraint to deal with fractions-of-a-second in a robust way?

推荐答案

范围类型 由下边框和上边框组成，可以包含或排除.典型的用例(以及范围类型的默认值)是包括下限和排除上限.

Range types consist of a lower and an upper border, which can be included or excluded. The typical use case (and default for range types) is to include the lower and exclude the upper bound.

排除重叠范围似乎很清楚.手册中有一个不错的代码示例

Excluding overlapping ranges seems clear. There is a nice code example in the manual

此外，创建另一个排除约束，使用 相邻运算符 -|- 也排除相邻条目.两者都必须基于 GiST 索引，因为目前不支持 GIN.

In addition, create another exclusion constraint employing the adjacent operator -|- to also exclude adjacent entries. Both must be based on GiST indexes as GIN is currently not supported for this.

为了保持干净，我会对所有带有 CHECK 约束 使用 范围函数:

To keep it clean, I'd enforce [) bounds (including lower and excluding upper) for all entries with a CHECK constraint using range functions:

CREATE TABLE tbl (
   tbl_id serial PRIMARY KEY
 , tsr tsrange
 , CONSTRAINT tsr_no_overlap  EXCLUDE USING gist (tsr WITH &&)
 , CONSTRAINT tsr_no_adjacent EXCLUDE USING gist (tsr WITH -|-)
 , CONSTRAINT tsr_enforce_bounds CHECK (lower_inc(tsr) AND NOT upper_inc(tsr))
);

db<>fiddle 这里
(旧的 SQL Fiddle)

_{不幸的是，这会创建两个相同的 GiST 索引来实现两个排除约束，逻辑上一个就足够了.这似乎是当前实现的一个缺点(至少到 Postgres 11).}

_{Unfortunately, this creates two identical GiST indexes to implement both exclusion constraints, where one would suffice, logically. That seems to be a shortcoming of the current implementation (up to at least Postgres 11).}

这篇关于在 PostgreSQL 中使用 EXCLUDE 防止相邻/重叠条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！