Postgres CHECK约束先于ON CONFLICT执行导致Upsert报错如何解决？

问题根源 #

PostgreSQL的INSERT ... ON CONFLICT逻辑执行时，VALUES传入的待插入行会优先触发表上的非延迟CHECK约束校验，无论后续是否会命中ON CONFLICT分支走更新逻辑，因此你传入负数的待插入行还没走到UPDATE逻辑就会触发校验报错。

可行解决方案 #

两种方案均为原子操作，不需要拆分SELECT和INSERT/UPDATE，同时保留原quantity >= 0的校验逻辑：

方案1：CTE先尝试更新，无匹配行再插入（无需修改表结构） #

通过CTE优先执行更新逻辑，只有更新命中0行（即对应item_id+date记录不存在）时，才执行插入，同时插入前判断待写入的初始值是否符合CHECK约束，避免非法值触发报错：

WITH update_attempt AS (
    -- 先尝试更新已存在的记录
    UPDATE stock
    SET quantity = quantity + -1 -- 替换为你的实际变动值
    WHERE item_id = 2 AND date = '2021-10-03' -- 替换为实际商品ID和日期
    RETURNING 1
)
-- 只有更新未命中时才执行插入
INSERT INTO stock (item_id, date, quantity)
SELECT 2, '2021-10-03', -1 -- 替换为实际参数
WHERE NOT EXISTS (SELECT 1 FROM update_attempt)
-- 插入前校验初始值合法性，避免负数插入触发CHECK报错
AND -1 >= 0; -- 替换为你的实际变动值

该方案优势是完全不需要修改原有表结构，兼容性最好。如果记录不存在时你传入的变动值为负，语句不会执行插入，自然不会触发约束报错，符合业务逻辑（初始库存不可能为负）。

方案2：将CHECK约束设置为延迟校验（无需修改原有Upsert语句） #

PostgreSQL支持将约束设置为语句执行完成/事务提交后再校验，只要最终写入的行数据符合约束即可，不会校验中间临时值：

-- 先删除原有非延迟CHECK约束
ALTER TABLE stock DROP CONSTRAINT stock_quantity_check;
-- 新建延迟校验的CHECK约束，INITIALLY DEFERRED表示默认在事务提交时校验
ALTER TABLE stock ADD CONSTRAINT stock_quantity_check CHECK (quantity >= 0) DEFERRABLE INITIALLY DEFERRED;

修改约束后，你原有的INSERT ... ON CONFLICT语句可以直接正常运行，约束只会校验Upsert执行完成后的最终quantity值，不会校验INSERT阶段传入的临时负值。

内容的提问来源于stack exchange，提问作者Iván