如何确保条目具有不重叠的时间范围？

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排除使用错误：数据类型整数没有默认的运算符类
• 如何在PostgreSQL中使用（安装）dblink？

然后：

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)可以做到这一点。

也许，更好的设计将是您的registration表与新registration_range表中的1-N条目之间的一对多关系。和一些逻辑来确定当前有效的条目（对于任何给定的时间点）。取决于更多未公开的信息。