PostgreSQL：去重插入且保留列子集的唯一性约束

你需要的是一种「严格去重插入」：基于(name, address)的唯一约束，仅当待插入行的业务列（x、y）与现有行完全一致时返回现有ID，不一致则报错，无冲突则插入新行。以下是两种可行的实现方式：

方法一：用ON CONFLICT结合条件抛出错误 #

PostgreSQL没有原生的REQUIRE语法，但可以通过ON CONFLICT DO UPDATE配合条件判断模拟需求：当冲突行的x、y与待插入行不一致时主动抛出错误，一致则无操作（返回现有ID），无冲突则正常插入。

完整SQL语句 #

INSERT INTO places (name, address, x, y)
VALUES ('测试名称', '测试地址', 11.1, 22.2)
ON CONFLICT (name, address)
DO UPDATE SET
    -- 用无意义的赋值占位，通过CASE判断是否抛出错误
    x = CASE
        WHEN (EXCLUDED.x, EXCLUDED.y) = (places.x, places.y) THEN places.x
        ELSE (SELECT pg_raise('unique_violation', '地点属性不匹配：name和address已存在，但x/y与现有数据冲突'))
    END
RETURNING id;

逻辑说明 #

• 无冲突：正常插入新行，返回新生成的id
• 冲突且列一致：CASE分支返回原x值（相当于无更新），返回现有行的id
• 冲突且列不一致：执行pg_raise抛出unique_violation类型的错误，中断插入

方法二：用触发器实现严格匹配检查 #

如果觉得ON CONFLICT的写法不够直观，可以通过触发器前置检查逻辑：

步骤1：创建触发器函数 #

CREATE OR REPLACE FUNCTION check_place_consistency()
RETURNS TRIGGER AS $$
DECLARE
    existing_x FLOAT;
    existing_y FLOAT;
BEGIN
    -- 查询是否存在相同name+address的行
    SELECT x, y INTO existing_x, existing_y
    FROM places
    WHERE name = NEW.name AND address = NEW.address;

    -- 存在冲突且列不匹配时抛出错误
    IF FOUND AND (existing_x, existing_y) != (NEW.x, NEW.y) THEN
        RAISE EXCEPTION '地点信息冲突：name=%, address=%对应的x/y与现有数据不一致', NEW.name, NEW.address
        USING ERRCODE = 'unique_violation';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：绑定触发器到表 #

CREATE TRIGGER trigger_place_consistency_check
BEFORE INSERT ON places
FOR EACH ROW EXECUTE FUNCTION check_place_consistency();

插入语句 #

配合ON CONFLICT DO NOTHING实现返回现有ID的逻辑：

WITH insert_attempt AS (
    INSERT INTO places (name, address, x, y)
    VALUES ('测试名称', '测试地址', 11.1, 22.2)
    ON CONFLICT (name, address) DO NOTHING
    RETURNING id
)
-- 优先取插入结果，无结果则查现有ID
SELECT id FROM insert_attempt
UNION ALL
SELECT id FROM places WHERE name = '测试名称' AND address = '测试地址'
LIMIT 1;

逻辑说明 #

• 触发器仅负责检查冲突且列不匹配的情况，直接抛出错误
• ON CONFLICT DO NOTHING处理冲突且列一致的场景，此时通过后续查询返回现有ID
• 无冲突时正常插入，返回新ID

两种方式对比 #

• ON CONFLICT方式：无需额外对象，语句简洁，适合固定列的简单检查场景
• 触发器方式：逻辑拆分清晰，后续新增需要匹配的业务列时，仅需修改触发器函数，扩展性更好

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