PostgreSQL跨表更新带ON CONFLICT冲突处理的Upsert实现问题

问题解决方案 #

PostgreSQL 可以通过 INSERT ... ON CONFLICT（俗称 UPSERT）语法实现「存在则更新、不存在则插入」的需求，具体实现如下：

前置准备

首先需要给表B添加group和user字段的联合唯一约束，用于冲突判断：

ALTER TABLE "表B" ADD CONSTRAINT uk_group_user UNIQUE ("group", "user");

注：user和group都是PostgreSQL的保留关键字，作为字段名使用时必须用双引号包裹，避免语法报错。

最终执行SQL

INSERT INTO "表B" ("group", "user", quantity)
SELECT 'xyz', "user", quantity * 0.5
FROM "表A"
ON CONFLICT ("group", "user")
DO UPDATE SET quantity = EXCLUDED.quantity;

逻辑说明

• 首先从表A批量读取所有用户数据，计算出目标值（group为xyz，quantity为表A对应值的0.5倍），尝试插入表B
• 当触发group+user的唯一约束冲突时（即表B已经存在该用户的xyz组记录），就执行更新操作，将quantity覆盖为新计算的值
• 关键字EXCLUDED代表本次尝试插入但冲突的行数据，直接取它的quantity即可拿到计算好的0.5倍值，不需要再次关联表A查询

原有SQL的问题

1. 更新对象错误：你要修改的是表B的数据，原SQL写的是更新表A
2. 字段名不匹配：两张表的关联字段是user而非你写的username
3. 语法错误：字符串常量ace没有加单引号，group保留字段没有加双引号
4. 没有实现不存在时新增的逻辑

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