如何确保条目具有不重叠的时间范围? [英] How to ensure entries with non-overlapping time ranges?

问题描述

我需要确保我的数据库仅包含两个或两个以上列是唯一的条目.只需在这些列上使用 UNIQUE 约束即可轻松实现.

I need to ensure my database only contains entries where two or more of its columns are unique. This can easily be achieved with a UNIQUE constraint over those columns.

就我而言，我只需要在重叠的时间范围内禁止重复.该表具有 valid_from 和 valid_to 列.在某些情况下，可能需要先通过设置 valid_to = now 使活动条目到期，然后插入调整为 valid_from = now 和 valid_to =的新条目无限.

In my case, I need to forbid duplication only for overlapping time ranges. The table has valid_from and valid_to columns. In some cases one might first need to expire the active entry to by setting valid_to = now, and then inserting a new entry adjusted to valid_from = now and valid_to = infinity.

使用 UPDATE ，我似乎可以使先前的条目到期而不会出现任何问题，但是插入新条目似乎很麻烦，因为我的基本列当前为 UNIQUE ，因此无法再次添加.

I seem to be able to expire the prior entry without any problems using UPDATE, but inserting the new entry seems to be troublesome since my base columns are currently UNIQUE, and therefore can't be added again.

我考虑将 valid_from 和 valid_to 作为 UNIQUE 约束的一部分添加，但这只会使约束更加宽松，并允许重复和重叠的时间范围.

I thought of adding valid_from and valid_to as part of the UNIQUE constraint, but that would just make the constraint more loose, and allow duplicates and overlapping time ranges to exist.

如何设置约束以确保重叠的 valid_from 和 valid_to tsrange 不存在重复项?

How do I make a constraint to ensure that duplicates don't exist with overlapping valid_from and valid_to tsrange?

我似乎正在寻找使用GIST排除，但它似乎不支持多列?这似乎对我不起作用:

I seem to be looking for EXCLUDE USING GIST, but it does not seem to support multiple columns? This does not seem to work for me:

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 &&);

推荐答案

您处在正确的轨道上.但是排除约束的语法有点不同.

You were on the right track. But the syntax for exclusion constraints is slightly different.

根据未公开的表定义，您可能需要安装扩展名(附加模块) btree_gist 首先.每个db一次.在我的示例中是必需的，因为默认情况下未为类型 integer 安装必需的运算符类:

Depending on the undisclosed table definition, you may need to install the extension (additional module) btree_gist first. Once per db. It's needed for my example since the required operator class is not installed for type integer by default:

CREATE EXTENSION btree_gist;

请参阅:

• PostgreSQL排除使用错误:数据类型整数没有默认的运算符类
• 如何在PostgreSQL中使用(安装)dblink?/a>

然后:

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 &&)
);

每个列都需要使用其各自的运算符列出.

Each column needs to be listed with its respective operator.

您需要一个范围类型.您提到了单独的列 valid_from 和 valid_to .您还会在失败的命令中提及 tsrange 和 valid .令人困惑.假设有两个 timestamp 列，则表达式为 tsrange(valid_from，valid_to)的表达式索引即可.

And you need a range type. You mention separate columns valid_from and valid_to. And you also mention tsrange and valid in the failed command. That's confusing. Assuming two timestamp columns, an expression index with the expression tsrange(valid_from, valid_to) would do it.

相关:

• 在PostgreSQL中执行此小时的操作查询
• 非重叠的连续时间戳范围(tstzrange)的开放时间
• PostgreSQL 9.4查询与&&
一起加入TSTZRANGE时，速度会逐渐变慢
• 存储星期几和时间吗?

通常，应在 timestamp ( tsrange )上选择 timestamptz ( tstzrange ).参见:

Typically, timestamptz (tstzrange) should be chosen over timestamp (tsrange). See:

• 完全忽略Rails和PostgreSQL中的时区

也许，一个更好的设计是您的 registration 表和新 registration_range 表格.和一些逻辑来确定当前有效的条目(对于任何给定的时间点).取决于更多未公开的信息.

Maybe, a superior design would be a one-to-many relationship between your registration table and 1-N entries in a new registration_range table. And some logic to determine the currently valid entry (for any given point in time). Depends on more undisclosed information.

这篇关于如何确保条目具有不重叠的时间范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！