Column1 | Column2 | Column3 |
---|---|---|
Value1 | Value2 | Value3 |
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/。我的唯一问题是它不能处理空值作为结束时间戳,我考虑用无限值代替,但也不起作用。
CREATE CONSTRAINT TRIGGER
е№¶зј–е†™дёЂдёЄз‰№е®љзљ„е‡Ѕж•°жќҐж‰§иЎЊдЅ зљ„жЈЂжџҐгЂ‚ - vyegorov