PostgreSQL促销表如何添加禁止重叠日期的约束

PostgreSQL 原生支持通过*排他约束（Exclusion Constraint）*实现该需求，不需要额外写触发器或业务层校验，步骤如下：

步骤1：安装btree_gist扩展 #

排他约束需要用到gist索引类型，默认gist不支持文本类型的等值匹配，需要先启用官方扩展btree_gist：

CREATE EXTENSION IF NOT EXISTS btree_gist;

该扩展是PostgreSQL官方内置的可信扩展，无需额外下载安装。

步骤2：添加排他约束 #

你可以在建表时直接定义约束，也可以给已存在的表追加约束：

建表时直接定义 #

调整后的建表语句如下：

create table promotions
(
    product_id text,
    start_date date,
    end_date date,
    discount numeric(5, 4),
    -- 新增排他约束
    EXCLUDE USING gist (
        product_id WITH =,
        daterange(start_date, end_date, '[]') WITH &&
    )
);

已存在表追加约束 #

如果表已经创建完成，执行以下语句追加约束即可：

ALTER TABLE promotions
ADD CONSTRAINT no_overlapping_promotions_for_same_product
EXCLUDE USING gist (
    product_id WITH =,
    daterange(start_date, end_date, '[]') WITH &&
);

参数说明 #

• 约束规则翻译为：当两条记录的product_id相等，且二者的日期范围存在重叠（&&是范围重叠的判断操作符）时，拒绝写入
• 日期范围的第三个参数'[]'表示起止日期都包含在内，即如果A活动的end_date等于B活动的start_date，会被判定为重叠；如果你希望日期首尾相接不算重叠，改为'[)'即可

效果验证 #

插入第一条促销记录不会报错：

INSERT INTO promotions (product_id, start_date, end_date, discount)
VALUES ('P001', '2024-06-01', '2024-06-10', 0.8);

再插入同商品、日期重叠的记录会直接抛出约束错误：

-- 以下语句会执行失败，因为和已有的2024-06-01~2024-06-10重叠
INSERT INTO promotions (product_id, start_date, end_date, discount)
VALUES ('P001', '2024-06-05', '2024-06-15', 0.7);

内容的提问来源于stack exchange，提问作者Ramon Pérez