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

问题描述

我正在创建一个在PostgreSQL 9.2.4中存储任意日期/时间范围的数据库。我想对这个数据库设置一个约束，这个约束会迫使日期/时间范围不重叠，并且不相邻（因为两个相邻的范围可以表示为单个连续的范围）。

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

 添加约束重叠_exclude EXCLUDE使用GIST（
框（
点（
 extract（EPOCH FROMfrom） -  1，
 extract（EPOCH FROMfrom） -  1 
），
点（
 extract（EPOCH FROM to），
提取（EPOCH FROMto）
）
）WITH& 
）; 
  

来自和<$ c $的列 c>到都是 TIMESTAMP WITH TIMEZONE ，并且是以UTC为单位存储的日期/时间（在将数据插入到这些列之前转换为UTC我的应用程序，我的数据库的时区在postgresql.conf中设置为UTC）。

然而，我想我可能会遇到的问题是这个约束正在做出（不正确的）假设，没有时间增量小于一秒。

值得注意的是，对于我正在存储的特定数据，我只需要第二个决议。但是，由于SQL类型 timestamp 和 timestamptz 都有更高的分辨率，我觉得我可能还需要处理超过一秒。

我的问题是：是否有任何问题，简单地假设第二个解决方案，因为这是我的所有应用程序需要（或想要），或者，如果有，如何改变这个约束以一个稳健的方式处理一分之一秒？

解决方案

范围类型< a href =http://www.postgresql.org/docs/current/interactive/rangetypes.html#RANGETYPES-CONSTRUCT>由下边框和上边框组成，可以包含或排除。
典型的用例（和范围类型的默认值）是包含下限，

排除重叠范围似乎很清楚。 手册中的好代码示例

此外，使用相邻运营商 - | - 也排除相邻的条目。两者都必须基于 GiST 索引，因为目前不支持GIN。

我将执行 [] 所有条目的边界。您可以通过附加的 CHECK 约束，使用范围函数：

  CREATE TABLE tbl（
 tbl_id serial PRIMARY KEY 
，tsr tsrange 
，EXCLUDE使用gist（tsr WITH&&） - 不重叠
，EXCLUDE使用gist（tsr WITH  -  |  - ） - 没有相邻的
，CHECK（lower_inc（tsr）AND NOT upper_inc （tsr）） -  enforce [] bounds 
）; 
  

SQL Fiddle。

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).

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

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.

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 bound and exclude the upper bound.

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

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.

I would enforce [) bounds for all entries. You could do that with additional an CHECK constraint using range functions:

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.

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