唯一日期时间范围的 Postgres 约束 [英] Postgres constraint for unique datetime range
问题描述
我的表有两列:
startsAt
endsAt
两者都保存日期和时间.我想做以下约束:
如果两列都不为空,则startsAt 和endsAt 之间的范围不得与其他范围(来自其他行)重叠.
您可以保留单独的 timestamp
列并仍然使用 排除约束 表达式:
CREATE TABLE tbl (tbl_id 串行主键,starts_at 时间戳,ends_at 时间戳, EXCLUDE USING gist (tsrange(starts_at,ends_at) WITH &&) -- 没有重叠);
构建tsrange
值 没有明确的界限,因为 tsrange(starts_at,ends_at)
自动假定默认界限:包括下限和排除上限 - '[)'
,这通常是最好的.>
相关:
向现有表添加约束
ALTER TABLE tbl 添加约束 tbl_no_overlapping_time_ranges排除使用要点 (tsrange(starts_at,ends_at) WITH &&)
语法细节与CREATE TABLE代码>
.
My table has two columns:
startsAt
endsAt
Both hold date and time. I want to make following constraint:
IF both columns are NOT NULL then range between startsAt and endsAt must not overlap with other ranges (from other rows).
You can keep your separate timestamp
columns and still use an exclusion constraint on an expression:
CREATE TABLE tbl (
tbl_id serial PRIMARY KEY
, starts_at timestamp
, ends_at timestamp
, EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&) -- no overlapping
);
Constructing a tsrange
value without explicit bounds as tsrange(starts_at, ends_at)
automatically assumes default bounds: including lower and excluding upper - '[)'
, which is typically best.
Related:
Add constraint to existing table
ALTER TABLE tbl ADD CONSTRAINT tbl_no_overlapping_time_ranges
EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&)
Syntax details are the same as for CREATE TABLE
.
这篇关于唯一日期时间范围的 Postgres 约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!