PostgreSQL中能否执行原子性的多Upsert操作？

当然可以！PostgreSQL完全支持原子性的批量Upsert操作，而且比你写的多个独立INSERT ... ON CONFLICT语句更高效，还能严格保证原子性——要么所有操作全部成功，要么任何一处出错就整体回滚，不会出现部分生效的情况。

为什么不推荐多个独立语句？ #

你给出的三个单独INSERT语句，默认情况下每个都会作为独立的事务执行（除非显式包裹在事务中）。这不仅效率低，还可能出现“前两条成功，第三条失败”的部分执行情况，不符合原子性要求。

最优方案：单条SQL实现批量原子Upsert #

PostgreSQL允许你在单条INSERT语句中传入多条值，然后通过一次ON CONFLICT子句统一处理冲突，整个语句是一个原子单元。

基础批量Upsert（冲突时用新值更新）

如果冲突时你希望直接用本次插入的新值更新字段，可以这样写：

INSERT INTO students ("name", "age")
VALUES 
  ('sid', 23),
  ('jack', 24),
  ('tom', 20)
ON CONFLICT ("name") 
SET "age" = EXCLUDED."age";

这里的EXCLUDED是PostgreSQL提供的特殊虚拟表，代表原本要插入但触发了冲突的那条记录的数据。

自定义冲突更新值（如你的示例需求）

如果每条记录冲突时需要更新为特定的自定义值（比如sid冲突时age设为12，jack设为14），可以用CASE语句来实现：

INSERT INTO students ("name", "age")
VALUES 
  ('sid', 23),
  ('jack', 24),
  ('tom', 20)
ON CONFLICT ("name") 
SET "age" = CASE 
  WHEN EXCLUDED."name" = 'sid' THEN 12
  WHEN EXCLUDED."name" = 'jack' THEN 14
  WHEN EXCLUDED."name" = 'tom' THEN 13
  ELSE EXCLUDED."age"  -- 兜底，保持默认更新逻辑
END;

备选方案：显式事务包裹多个语句 #

如果你因为某些原因必须保留多个独立的INSERT语句，也可以将它们包裹在一个显式事务中，以此保证原子性：

BEGIN;
INSERT INTO students ("name","age") values ('sid',23) on conflict ("name") set "age"=12;
INSERT INTO students ("name","age") values ('jack',24) on conflict ("name") set "age"=14;
INSERT INTO students ("name","age") values ('tom',20) on conflict ("name") set "age"=13;
COMMIT;

不过这种方式的执行效率不如单条批量语句，因为数据库需要多次解析和执行SQL，所以更推荐前面的批量写法。

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