PostgreSQL中如何添加约束阻止新行程条目与现有条目时间重叠

你的CHECK约束无效的核心原因是：PostgreSQL的CHECK约束是行级约束，仅能验证当前行的字段取值，无法查询同表的其他记录做全局冲突检查，所以你的写法根本起不到阻止时间重叠的作用。

要实现全局的行程时间重叠校验，应该用排他约束（EXCLUDE CONSTRAINT），结合PostgreSQL的范围类型和GiST索引来完成，具体步骤如下：

1. 安装必要扩展（若未安装） #

排他约束使用GiST索引时，需要依赖btree_gist扩展，先执行以下命令安装：

CREATE EXTENSION IF NOT EXISTS btree_gist;

2. 创建排他约束 #

将Weekday（日期）和Starting_Time（时间）合并成完整时间戳，再结合Duration生成时间范围，通过排他约束阻止重叠范围的插入/更新：

ALTER TABLE tour
ADD CONSTRAINT exclude_tour_overlapping
EXCLUDE USING gist (
    -- 生成左闭右开的带时区时间戳范围，避免相邻时间被误判为重叠
    tstzrange(
        Weekday + Starting_Time,
        Weekday + Starting_Time + Duration,
        '[)'
    ) WITH &&
);

参数说明： #

• tstzrange(...)：把日期+时间+时长转换为带时区的时间戳范围，[)表示左闭右开区间（包含开始时间，不包含结束时间，符合行程时间的常规定义）
• WITH &&：&&是PostgreSQL的范围重叠运算符，只要新行程的时间范围与现有任意行程范围重叠，就会触发约束，阻止操作

3. 验证约束效果 #

插入测试数据验证：

-- 插入正常行程
INSERT INTO tour (id, Starting_Time, Duration, Price, Weekday)
VALUES (1, '09:00:00', '2 hours', 100, '2024-05-20');

-- 插入重叠行程，会触发约束报错
INSERT INTO tour (id, Starting_Time, Duration, Price, Weekday)
VALUES (2, '10:00:00', '2 hours', 150, '2024-05-20');

执行第二条插入语句时，PostgreSQL会返回类似如下的错误，说明约束生效：

ERROR: conflicting key value violates exclusion constraint "exclude_tour_overlapping"
DETAIL: Key (tstzrange((weekday + starting_time), (weekday + starting_time + duration), '[)'::text))=(["2024-05-20 10:00:00+08","2024-05-20 12:00:00+08")) conflicts with existing key (tstzrange((weekday + starting_time), (weekday + starting_time + duration), '[)'::text))=(["2024-05-20 09:00:00+08","2024-05-20 11:00:00+08")).

内容的提问来源于stack exchange，提问作者Erd4