sql - Postgres 日期重叠约束

Question

我有一个这样的表:

date_start    date_end     account_id    product_id
2001-01-01    2001-01-31   1             1
2001-02-01    2001-02-20   1             1
2001-04-01    2001-05-20   1             1

我想禁止给定 (account_id, product_id) 的重叠间隔

编辑:我发现了一些东西:

CREATE TABLE test (                                                                                                
    from_ts TIMESTAMPTZ,
    to_ts TIMESTAMPTZ,
    account_id INTEGER,
    product_id INTEGER,
    CHECK ( from_ts < to_ts ),
    CONSTRAINT overlapping_times EXCLUDE USING GIST (
        account_id WITH =,
        product_id WITH =,
        box(
            point( extract(epoch FROM from_ts at time zone 'UTC'), extract(epoch FROM from_ts at time zone 'UTC') ),
            point( extract(epoch FROM to_ts at time zone 'UTC') , extract(epoch FROM to_ts at time zone 'UTC') )
        ) WITH &&
    )
);

如果你想了解更多关于这个http://www.depesz.com/2010/01/03/waiting-for-8-5-exclusion-constraints/

我唯一的问题是它不适用于空值作为结束时间戳，我想用无限值替换它但效果不佳。

Answer 1

好吧，我最终这样做了:

CREATE TABLE test (
    from_ts TIMESTAMPTZ,
    to_ts TIMESTAMPTZ,
    account_id INTEGER DEFAULT 1,
    product_id INTEGER DEFAULT 1,
    CHECK ( from_ts < to_ts ),
    CONSTRAINT overlapping_times EXCLUDE USING GIST (
        account_id WITH =,
        product_id WITH =,
        period(from_ts, CASE WHEN to_ts IS NULL THEN 'infinity' ELSE to_ts END) WITH &&
    )
);

与无穷大、交易证明完美配合。

我只需要安装将在 postgres 9.2 中原生的时间扩展和 btree_gist 在 9.1 中作为扩展可用 CREATE EXTENSION btree_gist;

nb:如果您没有空时间戳，则无需使用时间扩展，您可以使用我的问题中指定的 box 方法。