Postgres 13.3中如何为指定时间后的email字段添加唯一约束？

为什么你的原语句报错 #

PostgreSQL 13.3 中，ALTER TABLE ... ADD UNIQUE 语法不支持直接附加 WHERE 条件，唯一约束本身无法实现“仅对部分记录生效”的逻辑；而检查约束只能校验单条记录的字段规则，无法跨记录检查唯一性，所以两种方式都会报错。

根据你的需求，分两种场景提供解决方案：

场景1：仅要求 cutoff 时间后插入的记录，彼此间 email 唯一 #

如果只需要保证当前时间之后新增的记录之间 email 不重复（历史重复数据无需处理），最高效的方式是创建部分唯一索引：

1. 先获取当前时间作为约束生效的 cutoff 点：

SELECT now() AS cutoff_time;

2. 用得到的具体时间创建部分唯一索引：

-- 替换为你查询到的 cutoff_time，比如 '2024-05-20 14:30:00'
CREATE UNIQUE INDEX idx_people_email_unique_post_cutoff ON draft.people (email)
WHERE created_at > '2024-05-20 14:30:00';

这个索引只会对 created_at 在 cutoff 时间之后的记录生效，确保它们的 email 唯一，历史重复数据不受影响。

场景2：要求 cutoff 时间后插入的记录，email 不能与任何已存在记录（包括历史）重复 #

如果需要严格保证新插入的记录（cutoff后）的 email 不存在于整个表中，就需要通过触发器函数来实现：

步骤1：创建校验函数 #

CREATE OR REPLACE FUNCTION check_people_email_unique_new()
RETURNS TRIGGER AS $$
BEGIN
    -- 仅对 cutoff 时间后的新记录做校验
    IF NEW.created_at > '2024-05-20 14:30:00' THEN
        -- 检查该 email 是否已存在于表中
        IF EXISTS (
            SELECT 1 FROM draft.people WHERE email = NEW.email
        ) THEN
            RAISE EXCEPTION 'Email % 已存在', NEW.email;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：创建插入前触发器 #

CREATE TRIGGER trigger_people_check_email_unique
BEFORE INSERT ON draft.people
FOR EACH ROW EXECUTE FUNCTION check_people_email_unique_new();

（可选）优化性能 #

如果表数据量较大，为了加快校验时的查询速度，可以给 email 字段创建普通索引：

CREATE INDEX idx_people_email ON draft.people (email);

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