如何修复PostgreSQL upsert语句实现客户分类别信用额度批量增减及约束校验

问题根源 #

你原有语句的问题根源是PostgreSQL默认的非延迟CHECK约束会在每行INSERT操作执行时立刻校验，还没触发ON CONFLICT更新逻辑，负的插入值就直接被拦截了。以下是两种适配不同版本的解决方案：

方案1：PostgreSQL 15+ 推荐使用MERGE语句 #

PostgreSQL 15开始支持标准SQL的MERGE语法，天然适配这种多条件UPSERT场景，不需要修改原有表结构，逻辑更直观：

MERGE INTO credit AS target
USING (
    VALUES
        -- 此处替换为你的批量入参，顺序为client、category、amountToBeInserted（变动额度delta）
        ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid, '7c9e6679-7425-40de-944b-e07fc1f90ae7'::uuid, 100),
        ('b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12'::uuid, '8c9e6679-7425-40de-944b-e07fc1f90ae8'::uuid, -50)
) AS source (client, category, delta)
-- 匹配条件：同一用户同一类别
ON target.client = source.client AND target.category = source.category
-- 匹配到已有记录：累加额度
WHEN MATCHED THEN
    UPDATE SET amount = target.amount + source.delta
-- 未匹配到记录：直接插入delta作为初始额度
WHEN NOT MATCHED THEN
    INSERT (client, category, amount)
    VALUES (source.client, source.category, source.delta);

该语句完全满足你的所有要求：

• 支持批量传入多用户多类别的调整参数
• 无记录时正delta正常插入，负delta插入触发CHECK约束直接失败
• 有记录时累加后额度为负也会触发CHECK约束，整个语句回滚
• 单条语句执行，配合参数按client,category排序传入即可避免死锁

方案2：PostgreSQL 15及以下版本，修改约束为延迟校验 #

如果你的数据库版本不支持MERGE，只需要修改原有CHECK约束为延迟校验，不需要改动你已经写好的INSERT ON CONFLICT语句：

-- 修改约束为延迟校验，会等到整个语句/事务执行完成后再统一校验额度合法性
ALTER TABLE credit 
DROP CONSTRAINT CreditNotNegativeConstraint,
ADD CONSTRAINT CreditNotNegativeConstraint CHECK (amount >= 0) DEFERRABLE INITIALLY DEFERRED;

修改后你的原有语句就可以正常运行：

• 插入负delta时不会立刻触发约束校验，会先执行ON CONFLICT的更新逻辑
• 整个语句执行完成后统一校验所有行的额度是否非负，不符合则整体回滚，完全符合你的规则要求

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