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

问题描述

我在PostgreSQL 9.2.4中创建了一个存储任意日期/时间范围的数据库。我想对此数据库设置一个约束，强制日期/时间范围为非重叠和不相邻（因为两个相邻的范围可以表示为单个连续范围）。

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

  ADD CONSTRAINT overlap_exclude EXCLUDE使用GIST（
盒（
 extract（EPOCH FROMfrom） -  1，
 extract（EPOCH FROMfrom） -  1 
），
提取to），
 extract（EPOCH FROMto）
）
）WITH&& 
）; 
     c>到都是 TIMESTAMP WITHOUT TIME ZONE ，并且是以UTC格式存储的日期/时间（在将数据插入到这些列之前转换为UTC我的应用程序，我有我的数据库的时区设置为UTC在postgresql.conf）。
 
 
 
我想我可能有的问题是，这个约束正在做出（不正确的）假设，没有小于一秒的时间增量。
 
 
 
值得注意的是，对于我存储的特定数据，第二分辨率。但是，我觉得我仍然需要处理这个，因为SQL类型 timestamp 和 timestamptz 都是更高的分辨率
 
 
 
我的问题是：是否有任何问题，只是假设第二个解决方案，因为这是我所有的应用程序需要（或想要），或者，如果，我如何改变这个约束以鲁棒的方式处理分数？a / b> 
 
解决方案
范围类型< a href =http://www.postgresql.org/docs/current/interactive/rangetypes.html#RANGETYPES-CONSTRUCT =nofollow>由下边框和上边框组成，可以包含或排除。 
典型的用例（和范围类型的默认值）是包含下限和排除上限。
 
 
 
排除重叠范围似乎很清楚。手册中有漂亮的代码示例 
此外，使用相邻运算符   -  |  -    也排除 >条目。两者必须基于 GiST 索引，因为目前不支持GIN。
 
 
 
我将强制执行 [）所有条目的边界。您可以使用其他  CHECK 约束，使用 range functions ：
  CREATE TABLE tbl（
 tbl_id serial PRIMARY KEY 
，tsr tsrange 
，EXCLUDE使用gist（tsr WITH&&） - 不重叠
，EXCLUDE USING 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屋！