Postgres 16.2使用ON CONFLICT DO NOTHING仍触发约束违例错误排查

问题根源 #

ON CONFLICT DO NOTHING 仅能处理主键、唯一约束引发的冲突，对CHECK约束、非空约束这类数据校验错误完全无效。你的错误是违反了chk_actief_upn这个CHECK约束（规则为：当actief为true时，upn不能为null），这种情况会直接抛出错误，让事务进入中止状态，必须回滚才能继续操作。

解决方案 #

1. 提前校验数据，避免触发CHECK约束 #

在INSERT时先确保数据符合CHECK规则，比如给upn赋值，或者将actief设为false：

BEGIN;
-- 修正数据，满足CHECK约束：actief为true时upn不为null
INSERT INTO suez.gebruikers(cprid, upn, inlognaam, peoplesoft_id, actief
, locatie, naam, familienaam, voorvoegsels, initialen, voornamen
, email, opmerking)
VALUES (74965, 'j.e.test@amsterdamumc.nl', null, null, true, 'VUMC', 'Test JEF Justine',
'Test', null, 'JEF', 'Justine', 'j.e.test@amsterdamumc.nl', null)
ON CONFLICT DO NOTHING;
-- 事务仍活跃，可继续执行后续操作
SELECT * FROM suez.gebruikers WHERE cprid = 74965;
COMMIT;

2. 使用PL/pgSQL捕获异常，避免事务中止 #

如果无法提前校验数据，或需要保留原始数据尝试插入，可以用PL/pgSQL的异常处理块捕获错误，这样即使INSERT失败，事务也不会中止：

BEGIN;
-- 用DO块执行带异常捕获的INSERT
DO $$
BEGIN
    INSERT INTO suez.gebruikers(cprid, upn, inlognaam, peoplesoft_id, actief
    , locatie, naam, familienaam, voorvoegsels, initialen, voornamen
    , email, opmerking)
    VALUES (74965, null, null, null, true, 'VUMC', 'Test JEF Justine',
    'Test', null, 'JEF', 'Justine', 'j.e.test@amsterdamumc.nl', null)
    ON CONFLICT DO NOTHING;
EXCEPTION
    WHEN check_violation THEN
        -- 捕获CHECK约束错误，可选择记录日志或直接忽略
        NULL;
END $$;
-- 事务仍处于活跃状态，可继续执行查询
SELECT * FROM suez.gebruikers WHERE cprid = 74965;
COMMIT;

3. 调整CHECK约束（谨慎操作） #

如果业务逻辑允许，可以修改CHECK约束放宽规则，但需评估对现有数据和业务流程的影响：

-- 删除原有CHECK约束
ALTER TABLE suez.gebruikers DROP CONSTRAINT chk_actief_upn;
-- 添加调整后的约束（示例：允许actief为true时upn为空，仅业务允许时使用）
ALTER TABLE suez.gebruikers ADD CONSTRAINT chk_actief_upn CHECK (actief = false OR upn IS NOT NULL);

关键提示 #

• 事务一旦因ERROR进入中止状态，除了执行ROLLBACK回滚，无法进行任何其他操作，因此必须通过提前校验或异常捕获避免触发错误。
• ON CONFLICT的作用范围仅限唯一/主键冲突，不要期望它处理所有插入类错误。

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