Postgres日期重叠约束 [英] Postgres date overlapping constraint
问题描述
我有一个这样的表:
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)
编辑:我发现了一些东西:
I found something:
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 /
我唯一的问题是它不适用于空值作为结束时间戳,我想将其替换为无限值
My only problem is that it doesn't work with null values as a ending timestamp, I thought of replace it with infinite values but does not work as well.
推荐答案
好,我最终这样做了:
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 &&
)
);
与无穷大,交易证明完美配合。
Works perfectly with infinity, transaction proof.
我只需要安装时间扩展,该时间扩展将在postgres 9.2中原生,而btree_gist可在9.1中作为扩展使用。创建扩展btree_gist;
I just had to install temporal extension which is going to be native in postgres 9.2 and btree_gist available as an extension in 9.1 CREATE EXTENSION btree_gist;
nb:如果您没有null时间戳,则无需使用时间扩展,可以使用我的问题中指定的box方法。
nb : if you don't have null timestamp there is no need to use the temporal extension you could go with the box method as specified in my question.
这篇关于Postgres日期重叠约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!