PostgreSQL如何判断Upsert操作是插入新行还是更新已有行？

你当前用max(id) = id来判断的方法存在明显缺陷：

• 逻辑不严谨：如果表中已有比当前操作id更大的行，新插入的行id不是最大值时，会被误判为更新；如果更新的恰好是当前最大id的行，又会被误判为插入。
• 并发场景不可靠：多个事务同时操作时，max(id)的结果可能被其他事务的插入操作影响，导致判断完全错误。

PostgreSQL提供了更可靠的方式——利用系统列xmax来判断：

• 当执行插入操作时，新行的xmax值为0（因为没有被更新或删除过）
• 当执行更新操作时，被更新行的xmax会被设置为当前事务的ID（非0值）

直接在RETURNING子句里判断xmax即可得到准确结果：

INSERT INTO mytable (id, mycolumn)
VALUES (123, 'abc')
ON CONFLICT DO UPDATE
SET mycolumn = 'xyz'
RETURNING (xmax = 0) AS inserted;

返回结果中：

• inserted为true表示本次操作是插入新行
• inserted为false表示本次操作是更新已有行

这种方法的优势：

• 不依赖业务字段（比如id是否自增、是否为最大值），逻辑完全可靠
• 行级系统字段不受其他事务影响，并发场景下依然准确
• 无需额外查询表，性能高效

内容的提问来源于stack exchange，提问作者meme-lord