使用PostgreSQL中的EXCLUDE避免相邻/重叠条目

Question

我正在创建一个数据库，用于存储PostgreSQL 9.2.4中任意的日期/时间范围。 我想在这个数据库上设置一个限制条件，强制日期/时间范围不重叠，也不相邻（因为两个相邻的范围可以表示为一个连续范围）。

为了做到这一点，我使用了带有GiST索引的EXCLUDE约束。 这是我目前拥有的约束：

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”）。

然而我担心的问题是，这个约束条件可能会错误地假定时间增量不小于一秒。

值得注意的是，对于我存储的特定数据，我只需要秒级分辨率。但是，我认为我仍然需要处理这个问题，因为SQL类型timestamp和timestamptz比一秒钟更高的分辨率。

我的问题要么是：假设我的应用程序只需要（或希望）秒级分辨率，那么只假定秒级分辨率是否有问题？如果有问题，如何以健壮的方式修改此约束条件来处理小于一秒的时间分数？

- CmdrMoozy

Answer 1

范围类型由下限和上限组成，每个都可以包含或排除。
规范形式（也是范围类型的默认形式）是将下限包含，将上限排除。

包容性边界 '[]'

您可以包括下限和上限（[]），并使用CHECK约束和范围函数进行强制执行。

此时，“相邻”的范围会重叠。排除重叠范围似乎很清楚。手册中有一个代码示例。

CREATE TABLE tbl (
   tbl_id serial PRIMARY KEY
 , tsr tsrange
 , CONSTRAINT tsr_no_overlap EXCLUDE USING gist (tsr WITH &&)
 , CONSTRAINT tsr_enforce_incl_bounds CHECK (lower_inc(tsr) AND upper_inc(tsr))  -- 所有边界都包含在内！
);

只允许使用包容性边界的范围：

INSERT INTO tbl(tsr) VALUES ('[2013-10-22 00:00, 2013-10-22 01:00]');

在 这里 查看 db<>fiddle。

规范的边界'[)'

强制实施 [) 边界（包括下限，但不包括上限）。

此外，还创建另一个排除约束，采用 相邻运算符 -|- 以同时排除相邻的条目。两者都基于 GiST 索引，因为GIN目前不支持此功能。

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。
_{旧版 sqlfiddle}

不幸的是，这会创建两个相同的GiST索引来实现两个排除约束，而从逻辑上讲一个就足够了。

- Erwin Brandstetter

---

这个解决方案将非常有效。虽然我本可以使用单独的列来模拟，但改用tsrange会使这变得十分简单，并且还能使用其他范围操作符，这对于使用和管理这些数据非常有帮助。谢谢！ :) - CmdrMoozy

如果我想在插入相邻的tsr值时使用ON CONFLICT子句，这是否有用？我想知道如果插入相邻的tsr值，ON CONFLICT(tsr)是否会触发。 - madtyn

@madtyn：是的，ON CONFLICT子句涵盖了与EXCLUSION约束冲突的情况，但仅适用于DO NOTHING。手册： 请注意，排除约束不支持作为具有ON CONFLICT DO UPDATE的仲裁者。 请考虑我上面添加的更新的db<>fiddle中的演示。 - Erwin Brandstetter

Answer 2

您可以使用9.2中引入的范围类型重写排除。更好的方法是，您可以用一个范围替换这两个字段。在此处查看“范围约束”，其中包含基本上等同于您用例的示例：

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

- Denis de Bernardy

---

问题是，除了非重叠约束外，我还想防止相邻条目。如果我们让unit成为这些类型存储的最小时间单位，则对于任何两个条目fromA - toA和fromB - toB，如果toA + unit = fromB（或反之亦然），则它们可以表示为单个条目fromA - toB。我的问题是我不知道unit是什么或是否定义了，所以我目前使用1（秒）的值。 - CmdrMoozy

单位是微秒的一小部分，除非您使用时间戳（0）将其强制转换为秒，并偶尔出现舍入问题，使您遇到排除约束。对于相邻约束，我敢打赌您可以排除一个表达式，例如 ((during + interval '1 second') with &&)。 - Denis de Bernardy

Answer 3

我认为可能存在的问题是，这个限制条件假设没有小于一秒钟的时间增量。

你的想法没错，考虑以下内容：

select 
  extract ('epoch' from now())
  , extract ('epoch' from now()::timestamp(0))

- murison