唯一的datetime范围的Postgres约束 [英] Postgres constraint for unique datetime range
问题描述
我的表格有两列:
-
startsAt
-
endsAt
startsAt
endsAt
两者均保留日期和时间。
我想创建以下约束:
Both hold date and time. I want to make following constraint:
如果两列都不是NULL,那么beginAt和endsAt之间的范围不能与其他范围(从其他行)重叠。
IF both columns are NOT NULL then range between startsAt and endsAt must not overlap with other ranges (from other rows).
推荐答案
您可以保留单独的 timestamp
列, a href =http://www.postgresql.org/docs/current/interactive/sql-createtable.html#SQL-CREATETABLE-EXCLUDE =nofollow> 排除约束 上的表达式:
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
);
构建 tsrange
值,不带明确的边界 tsrange(starts_at,ends_at )
自动假设默认边界:包括低于和排除upper - '[)'
,通常是最好的。
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.
SQL Fiddle. (on pg9.2 since pg9.3 is offline)
相关:
- Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
ALTER TABLE tbl ADD CONSTRAINT tbl_no_overlapping_time_ranges
EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&)
约束的名称是可选的。如果不对其命名,系统将分配一个默认名称。语法详细信息与 CREATE TABLE
。
A name for the constraint is optional. If you don't name it, the system assigns a default name. Syntax details are the same as for CREATE TABLE
.
这篇关于唯一的datetime范围的Postgres约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!