PostgreSQL 中的 Upsert（合并，重复更新时插入）

Upsert 描述一种数据库操作：键值不存在时插入一行，键值已存在时则处理该行。PostgreSQL 使用 INSERT ... ON CONFLICT 实现这种行为。下方示例仅展示 SQL 语法；本文没有提供 PostgreSQL 服务器或执行证据。

PostgreSQL 中的 UPSERT 是什么

UPSERT 一词结合了“更新”（update）和“插入”（insert）的概念，但它不是 PostgreSQL 的 SQL 关键字。PostgreSQL 使用 INSERT ... ON CONFLICT 语句，在插入过程中处理冲突。DO UPDATE 操作必须指定可用的唯一冲突目标；排除约束不能用于仲裁 DO UPDATE。DO NOTHING 操作可以省略冲突目标，以忽略由可用的唯一约束或排除约束覆盖的冲突。

假设 car 表有一个整数类型的 id 主键列和一个 name 列，当前包含 (1, 'Nissan GTR')、(2, 'Toyota Corolla') 和 (3, 'Mercedes Benz')。如果直接 INSERT (2, 'Toyota Vigo')，由于 id 2 已存在，操作会违反主键约束。根据所选的冲突操作，upsert 可以改为替换 id 2 对应行的名称，也可以保留该行不变。

在 PostgreSQL 中使用 INSERT ... ON CONFLICT 执行 Upsert

在插入语句中使用 ON CONFLICT。例如，插入 (2, 'Toyota Vigo') 时，使用 ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name，即可更新现有的 id 2 行。更新后的该行为 (2, 'Toyota Vigo')；其他行保持不变。

冲突目标 (id) 指定发生冲突时应触发操作的唯一键。它可以指定由唯一索引或唯一约束支持的列。如果使用约束名称更清晰，PostgreSQL 也支持 ON CONSTRAINT constraint_name 形式。应选择符合数据中重复记录判定规则的目标。若语句中的 DO NOTHING 省略冲突目标，它可以忽略与任何可用唯一约束或排除约束发生的冲突；DO UPDATE 则必须指定冲突目标。

选择 DO UPDATE 还是 DO NOTHING

当冲突行需要刷新或合并时，选择 DO UPDATE。它的 SET 子句只列出要修改的列，因此可以保留主键不变。当应以现有行为准、并且可以丢弃本次插入时，选择 DO NOTHING。例如，INSERT INTO car (id, name) VALUES (2, 'Toyota Vigo') ON CONFLICT (id) DO NOTHING 会让 (2, 'Toyota Corolla') 保持不变。

这两种操作代表不同的数据策略：更新可能覆盖较新的值或用户编辑过的值，而不执行任何操作则可能留下过时的值。应根据应用需求判断哪种行为合适，不要把其中任何一种视为在所有情况下都安全。单条 ON CONFLICT DO UPDATE 语句也不能在一个命令中多次更新同一现有行。如果多行插入中，所选冲突键包含重复值，应在提交前先对输入去重。

使用 EXCLUDED 并选择冲突目标

在 DO UPDATE 中，EXCLUDED 指的是因冲突而未能插入的那一行。它不是用来存放待处理行的表。在 SET name = EXCLUDED.name 中，PostgreSQL 会采用传入的名称；像 car.name 这样的引用则指向冲突行中已存储的值。你也可以组合这两个值，例如将计数器更新为原有值加上传入的增量。

DO UPDATE 操作可以添加 WHERE 条件，仅在满足应用规则时才修改行。即使该条件阻止了更新，PostgreSQL 仍会锁定冲突行。例如，版本检查可以仅在传入版本较新时更新，但调用方应考虑到可能没有任何行被更新。应结合起来选择唯一冲突目标和更新条件：目标定义哪些行算作重复记录，条件则定义何时修改重复行。

为什么自定义函数、触发器、顺序语句和 CTE 通常不是默认选择

自定义函数或触发器可以集中处理专门规则，但会增加调用方需要理解和维护的代码路径。触发器还会作为插入或更新的副作用运行，可能使行为不那么明显。对于常见的插入或更新需求，优先使用内置冲突子句；只有当自定义逻辑能够清晰表达语句本身难以表达的特定规则时，才采用它。

先检查再插入的顺序操作，例如先查询 id 2 是否存在、仅在不存在时才插入，会产生竞态：另一笔事务可能在检查后插入该 id。仅仅把这两条语句放在一个事务中，并不能保证检查和插入对于并发会话是原子的。唯一约束可以保护数据，而 INSERT ... ON CONFLICT 允许 PostgreSQL 在约束处处理相互竞争的插入。如果多步工作流需要更强的保证，应选择合适的锁定或隔离策略，并将序列化错误或唯一性错误纳入设计处理。

公用表表达式（CTE）的作用范围仅限于以 WITH 开始的那一条 SQL 语句。CTE 可以组织单条语句，但它的名称不能用于单独执行的 INSERT 或后续语句。因此，不能把一个 WITH 子句、后接更新 CTE、再后接单独插入的做法拆成多次调用，并把 CTE 当作临时表使用。对于常见的插入或更新需求，应在一条 INSERT ... ON CONFLICT 语句中处理冲突。只有当 CTE 能让一条确实包含多个部分的语句更易阅读时，才使用它。