PostgreSQL INSERT ... ON CONFLICT：精准实现唯一约束下的插入与更新
PostgreSQL 的 INSERT ... ON CONFLICT 语句提供原子化的 upsert 操作，支持根据唯一约束或索引自动处理冲突。通过 DO NOTHING 或 DO UPDATE 选择是忽略冲突或更新现有行，并利用 EXCLUDED 伪表获取待插入值。但单行原子性不保证业务级别的幂等性或并发安全，需手动处理重复键和并发索引维护问题。
本文内容
简明答案
PostgreSQL 的 INSERT ... ON CONFLICT 语句为数据库提供了原子化的 upsert 操作机制，即尝试插入数据，若违反唯一约束或索引（冲突目标），则根据 DO NOTHING 或 DO UPDATE 选择是忽略冲突或更新现有行。冲突目标必须明确指定唯一规则（如 PRIMARY KEY 或 UNIQUE 约束），而 EXCLUDED 伪表则提供了待插入值，用于更新操作。虽然每行操作原子化，但不保证业务级别的幂等性或并发安全，例如并发索引维护或其他约束可能导致错误。开发者需手动处理重复键、并发冲突和业务逻辑的量化问题。
明确唯一性规则
ON CONFLICT 子句用于处理违反唯一约束或唯一索引的情况。这些约束包括 PRIMARY KEY 或 UNIQUE 约束，以及自定义的唯一索引。文档明确指出，ON CONFLICT DO UPDATE 需要指定冲突目标（conflict_target），以明确哪些冲突触发更新操作。唯一性规则由数据库级别强制执行，而非应用逻辑。例如，表 inventory(sku text PRIMARY KEY, qty integer NOT NULL) 的主键约束 sku 就是唯一性规则，任何重复的 sku 插入都会触发冲突。
选择冲突目标
冲突目标可以通过唯一索引推断、命名列/表达式，或直接使用 ON CONFLICT ON CONSTRAINT 命名约束。推断方式通常更简洁。对于 inventory 表，主键 sku 的冲突目标可以通过 ON CONFLICT (sku) 推断。文档指出，推断会选择包含指定列/表达式的所有唯一索引。如果直接命名约束，则使用该约束关联的索引。对于部分唯一索引，必须在冲突目标中包含 WHERE 子句，以匹配索引的谓词。
谨慎使用 DO NOTHING
ON CONFLICT DO NOTHING 会静默忽略所有违反唯一约束或索引的行。冲突目标可选，若未指定，则忽略所有可用的唯一约束冲突。适用于仅插入新行且忽略重复数据的场景。例如：INSERT INTO inventory (sku, qty) VALUES ('A', 3) ON CONFLICT DO NOTHING; 若 SKU 'A' 已存在，则不执行任何操作。命令执行后返回的行数表示实际插入的行数（此例中为 0）。
从 EXCLUDED 值更新
ON CONFLICT DO UPDATE 会修改冲突的现有行。SET 子句中的 EXCLUDED 伪表提供了原始待插入的值。文档要求在引用列时不需要加表名。例如，对于 inventory 表：INSERT INTO inventory (sku, qty) VALUES ('A', 3) ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty; 会将现有行的 qty 替换为 3。若需要累加更新，必须显式引用现有行：SET qty = inventory.qty + EXCLUDED.qty。
跟踪两行示例
假设 inventory 初始状态为 ('A', 2)，执行以下命令：INSERT INTO inventory (sku, qty) VALUES ('A', 3), ('B', 5) ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;。SKU 'A' 的行冲突，触发更新，将其 qty 设为 3；SKU 'B' 的行无冲突，直接插入。命令返回 INSERT 0 2，表示处理了 2 行（1 行更新，1 行插入）。最终状态为 ('A', 3), ('B', 5)。
处理单个语句中的重复行
PostgreSQL 文档指出 ON CONFLICT DO UPDATE 是确定性的：单个命令不会对同一现有行执行多次更新。若输入中包含重复键（如 VALUES ('A', 3), ('A', 4)），会导致基数违规。这不是普通的重复键错误，而是在 ON CONFLICT 处理前发生的问题。应在提交语句前对输入进行去重或根据业务规则聚合重复键。例如，替换数量时需明确哪个观测值生效；累加数量时需决定是否求和。语句错误时，其更改不会部分成功。在显式事务中，应使用应用程序的回滚或保存点策略恢复。
理解并发行为
ON CONFLICT DO UPDATE 为每行提供原子化的 INSERT 或 UPDATE 结果，即使在高并发环境下。但文档警告，当 CREATE INDEX CONCURRENTLY 或 REINDEX CONCURRENTLY 运行在同一唯一索引上时，INSERT ... ON CONFLICT 可能因唯一性冲突而意外失败。语句会锁定冲突行。若两个并发事务尝试插入相同的键，一个会成功插入，另一个则会在现有行上执行 DO UPDATE 操作。
检查语句外的限制
原子化 upsert 不保证业务级别的幂等性。重试累加更新可能再次增加量值，除非应用程序对逻辑操作进行去重。其他约束、权限和触发器仍可能拒绝语句。默认情况下，可空唯一列将 NULL 视为不同值，允许多个 NULL 行，除非显式指定 NULLS NOT DISTINCT。部分唯一索引仅适用于其谓词，冲突目标推断必须选择合适的唯一性规则。
检查清单
- ON CONFLICT DO UPDATE 必须指定冲突目标（conflict_target），否则无法确定哪些冲突触发更新。
- EXCLUDED 伪表提供待插入值，用于 DO UPDATE SET 子句，引用列时无需加表名。
- 在 DO UPDATE 操作前，应对重复键进行去重或聚合，避免单个命令对同一现有行多次更新。
- ON CONFLICT DO UPDATE 是每行原子化的，但不自动累加量值；需显式写入表达式如 qty = inventory.qty + EXCLUDED.qty。
- 对于部分唯一索引，冲突目标必须包含 WHERE 子句以匹配索引谓词，否则 PostgreSQL 无法正确推断唯一性规则。
- 命令标记 INSERT 0 N 表示 N 行被插入或更新，其中 oid 总是 0。
- ON CONFLICT DO NOTHING 无冲突目标时，会忽略所有可用唯一约束的冲突。
- 直接命名约束（ON CONFLICT ON CONSTRAINT）会使用该约束关联的索引。
- 原子化插入或更新不保证成功：其他约束、权限、触发器或并发唯一索引维护可能导致错误。
- 默认情况下，可空唯一列将 NULL 视为不同值，允许多个 NULL 行，除非使用 NULLS NOT DISTINCT 明确说明。
适用范围
本指南仅介绍 PostgreSQL 的 INSERT ... ON CONFLICT 语法，并非通用于所有数据库。行级原子性不提供业务级别的幂等性或量化验证。开发者应在 DO UPDATE 前根据业务规则对重复键进行去重或聚合。并发索引维护、其他数据库检查或权限限制可能导致错误。示例 inventory 表仅用于说明，非实际执行测试。