PostgreSQL执行UPSERT查询时如何获取被插入或更新的变更条目信息

先修正现有语句的语法错误 #

当前编写的UPSERT语句存在语法疏漏：DO UPDATE子句内，"last_name"=EXCLUDED."last_name"和"role"=EXCLUDED."role"之间遗漏逗号，直接执行会触发语法报错。

实现思路 #

不需要新增表字段、不需要执行两次全表比对查询，借助PostgreSQL内置系统列xmax + UPSERT语法自带的RETURNING子句，就能在单条原子语句中拿到所有变更条目的操作类型：

• xmax是PostgreSQL自动维护的行级锁标记，本次语句新插入的行，xmax值恒为0
• 命中唯一约束、走DO UPDATE路径的行，会被当前语句加排他行锁，xmax值为非0

基于这个特性，可以灵活定制判断逻辑：

• 基础场景仅区分插入/更新：直接判断xmax是否为0即可
• 精细化场景需要识别「命中冲突但新旧值完全一致、无实际变更」的情况，可以额外加值比对逻辑，用IS NOT DISTINCT FROM兼容空值的相等判断，避免普通等值判断对NULL值的误判

可直接落地的SQL代码 #

首先确认之前的唯一约束已正常创建：

alter table public."test_table" add constraint "bk_constraint" unique ("businessKey");

修正语法错误、补充变更标记的完整UPSERT语句：

INSERT INTO public."test_table" ("first_name","last_name","role", "businessKey") 
SELECT 
  UNNEST(ARRAY['Jon','Anna','Bernhard']),
  UNNEST(ARRAY['Doe','Miller','Hill']),
  UNNEST(ARRAY['team member','team member','project lead']),
  UNNEST(ARRAY['Jon JD','Anna AM','Bernhard AH']) 
ON CONFLICT ("businessKey") 
DO UPDATE SET 
  "first_name" = EXCLUDED."first_name",
  "last_name" = EXCLUDED."last_name",
  "role" = EXCLUDED."role",
  "businessKey" = EXCLUDED."businessKey"
RETURNING 
  *,
  CASE 
    WHEN xmax = 0 THEN 'INSERT' 
    WHEN test_table.* IS NOT DISTINCT FROM EXCLUDED.* THEN 'NO_CHANGE'
    ELSE 'UPDATE' 
  END AS upsert_op_type;

返回结果说明 #

执行上述语句后，结果集会直接包含本次UPSERT涉及的所有条目，每一行的upsert_op_type字段会标记对应操作类型：

• INSERT：全新插入的条目
• UPDATE：命中冲突、且实际字段值发生变化的更新条目
• NO_CHANGE：命中冲突、但新旧字段值完全一致、没有实际修改的条目

返回的结果集可以直接被应用层读取对接后续业务流程，无需额外发起二次查询。

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