最近在项目里做数据链路改造碰到一个绕不开的需求上游系统推送过来的数据经常带着已经落过库的主键或者唯一键直接 INSERT 必然报 duplicate key先 SELECT 再判断又是典型的竞态写法并发一上来全是坑。折腾了一圈最后把重心放在 PostgreSQL 的 ON CONFLICT 子句上也就是常说的 UPSERT 语法。这次就把这条新增的特殊语句——ON CONFLICT ... DO (UPDATE SET ...)/(NOTHING)——从语法、原理到落地经验完整梳理一遍适合正在搞数据同步、批量导入、或者想把手写 upsert 逻辑替换掉的开发者参考。1. 需求拆解为什么要引入 ON CONFLICT 特殊处理1.1 业务场景重复键冲突是躲不掉的先说我遇到的实际场景。项目里有个订单同步模块上游通过消息队列推送订单变更同一个订单号在状态流转过程中会被推好几次而且为了保证消息不丢消费端还做了 at-least-once 的重试。这就意味着同一批数据里很有可能出现重复的主键。在没有 ON CONFLICT 之前业务代码一般这么写先根据主键查一次库存在就走 UPDATE不存在就走 INSERT。看起来没问题但仔细一推敲问题不少。首先是性能问题。每条数据都要多一次 SELECT 往返批量导入几千条时这个开销会被放大得很明显。更麻烦的是并发问题。两个服务实例同时处理同一个订单号都先 SELECT 发现不存在然后一起 INSERT后到的那个必然撞唯一索引报错。你要是为了规避这个错误再包一层异常捕获代码就越来越难看。还有的表结构带外键和审计日志用 DELETE 再 INSERT 的方式会把关联数据搅得一团糟触发器也会被误触发。ON CONFLICT 就是为这种场景设计的。它允许你在一条 INSERT 语句后面声明如果插入时撞了唯一键或主键不要直接报错而是执行一个指定的动作。这个动作可以是 DO NOTHING也就是静默跳过这条冲突数据也可以是 DO UPDATE SET也就是把这条数据直接改成更新操作。换句话说一条语句同时承载了插和改两种语义这正是 UPSERT 名字的由来。1.2 ON CONFLICT 解决的本质问题ON CONFLICT 解决的表面问题是重复键报错本质问题是判断与写入的原子性。在应用层实现 upsert无论怎么设计都逃不开 check-then-act 的竞态问题除非你上分布式锁或者事务加锁。而 ON CONFLICT 把这个判断交给了数据库执行器插入时如果检测到唯一索引冲突就在同一事务里决定是忽略还是更新整个过程对外是原子的。这一点在数据同步、事件溯源、缓存回填这类场景里价值非常大。另外如果你的项目里有一个自研的 SQL 处理层、查询改写器或者数据库中间件那么新增 ON CONFLICT 语句的特殊处理就是一项实打实的语法支持工作。你不能把这条语句当成普通 INSERT 丢掉因为它的行为和返回行数语义都不一样解析、校验、执行计划都要单独处理。后面的章节我会把语法层和实际使用层的要点都过一遍。2. 语法拆解与执行原理2.1 两种动作分支DO NOTHING 与 DO UPDATE SET先看完整语法我习惯用简化的形式记忆INSERT INTO table_name (column_list) VALUES (value_list) ON CONFLICT [(conflict_target)] DO NOTHING;INSERT INTO table_name (column_list) VALUES (value_list) ON CONFLICT (conflict_target) DO UPDATE SET column EXCLUDED.column, ...;两种动作分支有几个关键差异先记在心里DO NOTHING冲突时什么都不做语句不会报错但这一行也不会插入。如果这一条语句里还有其他不冲突的行它们照常插入。DO UPDATE SET冲突时执行更新更新的数据源来自 EXCLUDED 伪行。EXCLUDED 代表如果没冲突本来会插进去的那一行。只有 DO NOTHING 可以省略 conflict_targetDO UPDATE SET 必须显式指定冲突目标否则数据库不知道要仲裁哪个唯一索引也承担不起误更新的风险。为什么 DO UPDATE SET 必须带冲突目标假设表上有两个唯一索引冲突可能是由其中任何一个触发的如果你不告诉数据库根据哪个索引来判断更新行为就会变得不可预期。强制指定冲突目标等于把哪条唯一约束算数这件事定死了执行计划才能稳定。2.2 冲突目标怎么选冲突目标有三种常见写法列名列表ON CONFLICT (user_id)。要求表上存在与这些列匹配的唯一索引或唯一约束列的顺序最好和索引定义一致复合唯一键尤其要注意。约束名ON CONFLICT ON CONSTRAINT user_pkey。直接指定约束的名字不需要关心列怎么排适合主键和带名字的唯一约束。部分唯一索引ON CONFLICT (col) WHERE condition。如果唯一索引是带 WHERE 的部分索引冲突目标也要带上同样的条件否则匹配不上。另外还有一种特殊情况DO NOTHING 可以完全不指定冲突目标。这时候数据库会捕获任意一个唯一约束或排他约束触发的冲突适合只要别报错就行的幂等写入场景。但我个人在实践中不推荐大范围使用这种写法原因后面在踩坑小节里讲。2.3 执行器背后的逻辑唯一索引仲裁PostgreSQL 拿到 ON CONFLICT 子句后会先做一次索引推断index inference也就是拿 conflict_target 里的列或约束名去系统表 pg_index 里找匹配的唯一索引这个索引被称为仲裁索引arbiter index。如果找不到执行器直接报错there is no unique or exclusion constraint matching the ON CONFLICT specification。找到仲裁索引后插入流程就变成正常尝试插入新行如果触发了唯一索引冲突立即通过这个索引定位到已存在的冲突行随后根据动作分支要么什么都不做要么把新行的数据拿来更新旧行。为了保证并发事务下不出现两个事务同时成功插入同一个 key的情况PostgreSQL 内部用了 speculative insertion 机制插入过程发现可能有并发冲突时会等待或重新检查。这点不用应用层操心但我建议你在高并发压测时留意一下锁等待和死锁日志因为批量 upsert 的处理顺序不一致时还是会有死锁的风险。3. 实操在项目中新增 ON CONFLICT 处理3.1 场景一幂等写入 DO NOTHING先看最基础的需求埋点事件去重。假设消息队列里的同一事件会被投递多次我们只希望第一个到达的落库后面的直接忽略。表结构很简单CREATE TABLE event_log ( event_id varchar(64) PRIMARY KEY, payload jsonb, created_at timestamptz DEFAULT now() );写入语句INSERT INTO event_log (event_id, payload) VALUES (evt-1001, {type:click}) ON CONFLICT (event_id) DO NOTHING;执行之后如果 event_id 已经存在这条语句不会报错只是插入 0 行。配合 RETURNING 可以判断到底是插入了还是被忽略了INSERT INTO event_log (event_id, payload) VALUES (evt-1001, {type:click}) ON CONFLICT (event_id) DO NOTHING RETURNING event_id;如果返回了 event_id说明这次是真的插入了如果返回空集说明是重复消息。这个模式在做消息去重、任务重复投递兜底时非常管用代码里不用再写 SELECT 判断也不用 catch 唯一键异常。3.2 场景二增量更新 DO UPDATE SET EXCLUDED业务上更常见的是有就更新没有就插入。拿用户资料缓存来举例CREATE TABLE user_profile ( user_id bigint PRIMARY KEY, nickname text, score int, updated_at timestamptz DEFAULT now() );写入语句INSERT INTO user_profile (user_id, nickname, score) VALUES (1001, 老张, 85) ON CONFLICT (user_id) DO UPDATE SET nickname EXCLUDED.nickname, score EXCLUDED.score, updated_at now();这里 EXCLUDED 是核心它代表如果没冲突本来会插入的那一行。注意updated_at不能用EXCLUDED.updated_at除非你在 VALUES 里显式传了时间。我一般会用now()重新取当前时间语义更明确。这条语句执行时如果 user_id1001 不存在就是纯插入如果已存在就更新 nickname 和 score同时刷新 updated_at。3.3 场景三条件更新与 RETURNING 组合有些场景不能无条件覆盖旧数据。比如库存表只有新版本号比旧的大时才允许更新。这时候可以在 DO UPDATE SET 后面加 WHERE 条件INSERT INTO item_stock (item_id, stock, version) VALUES (2001, 50, 2) ON CONFLICT (item_id) DO UPDATE SET stock EXCLUDED.stock, version EXCLUDED.version WHERE item_stock.version EXCLUDED.version RETURNING item_id, stock, version;这个 WHERE 条件是作用在冲突后的更新动作上的不是作用在整条语句上的。只有条件为真时才会真正执行更新否则这一行会被跳过。上面的语句如果遇到旧版本号相同或者更大的情况就不会覆盖数据。RETURNING 在这里可以顺带把最终结果返回给应用层省一次查询。要注意的是RETURNING 只会返回真正执行了插入或更新的行被 DO NOTHING 跳过和被 WHERE 过滤掉的行都不会出现在结果集里。3.4 面向解析与翻译层新增语句的处理要点如果你的项目不是直接用 PostgreSQL而是自己写了 SQL 解析器、查询改写器或者兼容层那新增 ON CONFLICT 特殊处理是另一套工作量。我列几个关键点方便做技术方案时对号入座词法阶段要把 ON、CONFLICT、EXCLUDED 这些关键字纳入识别不能把它们当成普通标识符丢掉。语法树里应该在 InsertStmt 节点下挂一个 OnConflictClause 节点里面至少包含 conflict_target可空和 action两种动作分支两个字段都要有明确的空值语义。EXCLUDED 需要被解析成一个特殊的伪表引用它的列集合和目标表的列集合一致。绑定列时按列名解析而不是按位置。如果只是做语法转换不建议把 ON CONFLICT 简单展开成先 SELECT 再 INSERT/UPDATE的普通语句因为展开后无法保证原子性和并发安全。必须处理返回行数语义DO NOTHING 时冲突行不算成功插入影响行数要按实际插入/更新的行数算。这一点很多做数据库中间件的团队容易忽略。ON CONFLICT 不是普通 INSERT 的语法糖它的执行语义和结果集都不同改写前一定要想清楚。4. 常见问题与排查技巧实录4.1 错误信息速查表说一下我在实际开发里遇到最多的几个报错以及对应的处理办法错误信息出错原因解决办法there is no unique or exclusion constraint matching the ON CONFLICT specification冲突目标和你表上已有的唯一索引匹配不上检查列名拼写、列顺序、是否用了部分唯一索引但没带 WHEREON CONFLICT DO UPDATE command cannot affect row a second time同一条语句里两条待插入数据命中了同一个已存在的冲突行批量数据里先按冲突键去重或者拆成小批次执行invalid reference to FROM-clause entry for table xxxDO UPDATE SET 里错误引用了目标表之外的表更新想要的新值用 EXCLUDED引用当前旧值用目标表名插入行数一直是 0 但没报错这不是 bug是 DO NOTHING 的正常行为想区分插入和忽略用 RETURNING这里面最坑的是第一条。复合唯一键的列顺序和索引定义不一致或者部分唯一索引漏了 WHERE 条件都很容易出现明明有唯一索引但匹配不上的情况。排查的时候别只看错误信息直接查一下 pg_index 和 pg_constraint把索引定义原样抄到 ON CONFLICT 里最稳妥。4.2 性能与并发踩坑记录第一批坑在性能。ON CONFLICT 依赖唯一索引来定位冲突行表上没有对应的唯一约束时语句直接报错索引设计不合理时冲突检测的代价会很高。我在一个千万级的表上做过测试批量写入时一次塞的数据量越大单条语句的锁持有时间就越长建议每批控制在几百到一千行左右而不是一次性灌几万行。第二批坑在更新的副作用。DO UPDATE SET 即使新旧值完全一样也会走完整的更新流程产生新的行版本、触发更新触发器、把 updated_at 改掉。如果每次都拿相同数据来刷表会不断膨胀膨胀率上来了vacuum 都来不及回收。我习惯在更新条件里加一个保护ON CONFLICT (user_id) DO UPDATE SET nickname EXCLUDED.nickname, updated_at now() WHERE user_profile.nickname IS DISTINCT FROM EXCLUDED.nickname;这样只有 nickname 真的变了才会触发更新能显著减少无意义的写放大。注意这里要用 IS DISTINCT FROM而不是因为后者遇到 NULL 会得 UNKNOWN条件永远不成立。第三批坑在序列。即使 DO NOTHING插入尝试也会消耗序列值自增主键会出现明显的断层。这在逻辑上完全正常但如果有人把主键连续当业务约定就会踩坑。建议提前跟团队说清楚用了 ON CONFLICT 的表主键空洞是预期行为不是 bug。还有并发死锁。两个事务分别按不同顺序批量插入同一组 key 时可能形成锁环。比如事务 A 先插入 key1 再插入 key2事务 B 先插入 key2 再插入 key1两者同时在冲突更新阶段锁定了对方需要的行就会死锁。解决办法很粗暴也很有效批量数据先按主键排序所有事务都按同一个顺序处理死锁基本能消除。4.3 我的习惯写法与避坑心得最后分享几个我固定下来的写法。第一只要用了 DO UPDATE SET一定写显式冲突目标不要省略。省略冲突目标只对 DO NOTHING 合法但写清楚目标可以让执行计划稳定也方便后面代码 review 的人一眼看出依赖哪个唯一键。第二复合唯一键的冲突目标我永远保持和索引定义一致的列顺序省去排查的麻烦。第三如果想在一条语句里区分插入和更新可以借助 RETURNING 加一个小技巧。PostgreSQL 里新插入的行 xmax 为 0被更新后的行 xmax 不为 0所以可以这么写INSERT INTO user_profile (user_id, nickname, score) VALUES (1001, 老张, 85) ON CONFLICT (user_id) DO UPDATE SET score EXCLUDED.score RETURNING user_id, (xmax 0) AS inserted;这个写法不是官方文档推荐方案属于经验性的判断某些并发场景下不一定准确但它省事。如果对准确性要求极高我建议在表里加一个created_at字段来做区分插入时用 now()更新时不动它这样只要看 created_at 是否等于 updated_at 就能判断是不是本次新插入的可靠得多。说实话ON CONFLICT 这条语句我刚接触时觉得没什么技术含量就是语法糖。真正在项目里跑起来才发现它把应用层一大票并发处理逻辑简化掉了也让数据同步代码清爽了非常多。如果你的项目还在用先查后插的老套路强烈建议找个低峰期把核心写入路径切到 ON CONFLICT 上体感会很直接。 PostgreSQL UPSERT 实战：ON CONFLICT 语法原理与避坑指南
最近在项目里做数据链路改造碰到一个绕不开的需求上游系统推送过来的数据经常带着已经落过库的主键或者唯一键直接 INSERT 必然报 duplicate key先 SELECT 再判断又是典型的竞态写法并发一上来全是坑。折腾了一圈最后把重心放在 PostgreSQL 的 ON CONFLICT 子句上也就是常说的 UPSERT 语法。这次就把这条新增的特殊语句——ON CONFLICT ... DO (UPDATE SET ...)/(NOTHING)——从语法、原理到落地经验完整梳理一遍适合正在搞数据同步、批量导入、或者想把手写 upsert 逻辑替换掉的开发者参考。1. 需求拆解为什么要引入 ON CONFLICT 特殊处理1.1 业务场景重复键冲突是躲不掉的先说我遇到的实际场景。项目里有个订单同步模块上游通过消息队列推送订单变更同一个订单号在状态流转过程中会被推好几次而且为了保证消息不丢消费端还做了 at-least-once 的重试。这就意味着同一批数据里很有可能出现重复的主键。在没有 ON CONFLICT 之前业务代码一般这么写先根据主键查一次库存在就走 UPDATE不存在就走 INSERT。看起来没问题但仔细一推敲问题不少。首先是性能问题。每条数据都要多一次 SELECT 往返批量导入几千条时这个开销会被放大得很明显。更麻烦的是并发问题。两个服务实例同时处理同一个订单号都先 SELECT 发现不存在然后一起 INSERT后到的那个必然撞唯一索引报错。你要是为了规避这个错误再包一层异常捕获代码就越来越难看。还有的表结构带外键和审计日志用 DELETE 再 INSERT 的方式会把关联数据搅得一团糟触发器也会被误触发。ON CONFLICT 就是为这种场景设计的。它允许你在一条 INSERT 语句后面声明如果插入时撞了唯一键或主键不要直接报错而是执行一个指定的动作。这个动作可以是 DO NOTHING也就是静默跳过这条冲突数据也可以是 DO UPDATE SET也就是把这条数据直接改成更新操作。换句话说一条语句同时承载了插和改两种语义这正是 UPSERT 名字的由来。1.2 ON CONFLICT 解决的本质问题ON CONFLICT 解决的表面问题是重复键报错本质问题是判断与写入的原子性。在应用层实现 upsert无论怎么设计都逃不开 check-then-act 的竞态问题除非你上分布式锁或者事务加锁。而 ON CONFLICT 把这个判断交给了数据库执行器插入时如果检测到唯一索引冲突就在同一事务里决定是忽略还是更新整个过程对外是原子的。这一点在数据同步、事件溯源、缓存回填这类场景里价值非常大。另外如果你的项目里有一个自研的 SQL 处理层、查询改写器或者数据库中间件那么新增 ON CONFLICT 语句的特殊处理就是一项实打实的语法支持工作。你不能把这条语句当成普通 INSERT 丢掉因为它的行为和返回行数语义都不一样解析、校验、执行计划都要单独处理。后面的章节我会把语法层和实际使用层的要点都过一遍。2. 语法拆解与执行原理2.1 两种动作分支DO NOTHING 与 DO UPDATE SET先看完整语法我习惯用简化的形式记忆INSERT INTO table_name (column_list) VALUES (value_list) ON CONFLICT [(conflict_target)] DO NOTHING;INSERT INTO table_name (column_list) VALUES (value_list) ON CONFLICT (conflict_target) DO UPDATE SET column EXCLUDED.column, ...;两种动作分支有几个关键差异先记在心里DO NOTHING冲突时什么都不做语句不会报错但这一行也不会插入。如果这一条语句里还有其他不冲突的行它们照常插入。DO UPDATE SET冲突时执行更新更新的数据源来自 EXCLUDED 伪行。EXCLUDED 代表如果没冲突本来会插进去的那一行。只有 DO NOTHING 可以省略 conflict_targetDO UPDATE SET 必须显式指定冲突目标否则数据库不知道要仲裁哪个唯一索引也承担不起误更新的风险。为什么 DO UPDATE SET 必须带冲突目标假设表上有两个唯一索引冲突可能是由其中任何一个触发的如果你不告诉数据库根据哪个索引来判断更新行为就会变得不可预期。强制指定冲突目标等于把哪条唯一约束算数这件事定死了执行计划才能稳定。2.2 冲突目标怎么选冲突目标有三种常见写法列名列表ON CONFLICT (user_id)。要求表上存在与这些列匹配的唯一索引或唯一约束列的顺序最好和索引定义一致复合唯一键尤其要注意。约束名ON CONFLICT ON CONSTRAINT user_pkey。直接指定约束的名字不需要关心列怎么排适合主键和带名字的唯一约束。部分唯一索引ON CONFLICT (col) WHERE condition。如果唯一索引是带 WHERE 的部分索引冲突目标也要带上同样的条件否则匹配不上。另外还有一种特殊情况DO NOTHING 可以完全不指定冲突目标。这时候数据库会捕获任意一个唯一约束或排他约束触发的冲突适合只要别报错就行的幂等写入场景。但我个人在实践中不推荐大范围使用这种写法原因后面在踩坑小节里讲。2.3 执行器背后的逻辑唯一索引仲裁PostgreSQL 拿到 ON CONFLICT 子句后会先做一次索引推断index inference也就是拿 conflict_target 里的列或约束名去系统表 pg_index 里找匹配的唯一索引这个索引被称为仲裁索引arbiter index。如果找不到执行器直接报错there is no unique or exclusion constraint matching the ON CONFLICT specification。找到仲裁索引后插入流程就变成正常尝试插入新行如果触发了唯一索引冲突立即通过这个索引定位到已存在的冲突行随后根据动作分支要么什么都不做要么把新行的数据拿来更新旧行。为了保证并发事务下不出现两个事务同时成功插入同一个 key的情况PostgreSQL 内部用了 speculative insertion 机制插入过程发现可能有并发冲突时会等待或重新检查。这点不用应用层操心但我建议你在高并发压测时留意一下锁等待和死锁日志因为批量 upsert 的处理顺序不一致时还是会有死锁的风险。3. 实操在项目中新增 ON CONFLICT 处理3.1 场景一幂等写入 DO NOTHING先看最基础的需求埋点事件去重。假设消息队列里的同一事件会被投递多次我们只希望第一个到达的落库后面的直接忽略。表结构很简单CREATE TABLE event_log ( event_id varchar(64) PRIMARY KEY, payload jsonb, created_at timestamptz DEFAULT now() );写入语句INSERT INTO event_log (event_id, payload) VALUES (evt-1001, {type:click}) ON CONFLICT (event_id) DO NOTHING;执行之后如果 event_id 已经存在这条语句不会报错只是插入 0 行。配合 RETURNING 可以判断到底是插入了还是被忽略了INSERT INTO event_log (event_id, payload) VALUES (evt-1001, {type:click}) ON CONFLICT (event_id) DO NOTHING RETURNING event_id;如果返回了 event_id说明这次是真的插入了如果返回空集说明是重复消息。这个模式在做消息去重、任务重复投递兜底时非常管用代码里不用再写 SELECT 判断也不用 catch 唯一键异常。3.2 场景二增量更新 DO UPDATE SET EXCLUDED业务上更常见的是有就更新没有就插入。拿用户资料缓存来举例CREATE TABLE user_profile ( user_id bigint PRIMARY KEY, nickname text, score int, updated_at timestamptz DEFAULT now() );写入语句INSERT INTO user_profile (user_id, nickname, score) VALUES (1001, 老张, 85) ON CONFLICT (user_id) DO UPDATE SET nickname EXCLUDED.nickname, score EXCLUDED.score, updated_at now();这里 EXCLUDED 是核心它代表如果没冲突本来会插入的那一行。注意updated_at不能用EXCLUDED.updated_at除非你在 VALUES 里显式传了时间。我一般会用now()重新取当前时间语义更明确。这条语句执行时如果 user_id1001 不存在就是纯插入如果已存在就更新 nickname 和 score同时刷新 updated_at。3.3 场景三条件更新与 RETURNING 组合有些场景不能无条件覆盖旧数据。比如库存表只有新版本号比旧的大时才允许更新。这时候可以在 DO UPDATE SET 后面加 WHERE 条件INSERT INTO item_stock (item_id, stock, version) VALUES (2001, 50, 2) ON CONFLICT (item_id) DO UPDATE SET stock EXCLUDED.stock, version EXCLUDED.version WHERE item_stock.version EXCLUDED.version RETURNING item_id, stock, version;这个 WHERE 条件是作用在冲突后的更新动作上的不是作用在整条语句上的。只有条件为真时才会真正执行更新否则这一行会被跳过。上面的语句如果遇到旧版本号相同或者更大的情况就不会覆盖数据。RETURNING 在这里可以顺带把最终结果返回给应用层省一次查询。要注意的是RETURNING 只会返回真正执行了插入或更新的行被 DO NOTHING 跳过和被 WHERE 过滤掉的行都不会出现在结果集里。3.4 面向解析与翻译层新增语句的处理要点如果你的项目不是直接用 PostgreSQL而是自己写了 SQL 解析器、查询改写器或者兼容层那新增 ON CONFLICT 特殊处理是另一套工作量。我列几个关键点方便做技术方案时对号入座词法阶段要把 ON、CONFLICT、EXCLUDED 这些关键字纳入识别不能把它们当成普通标识符丢掉。语法树里应该在 InsertStmt 节点下挂一个 OnConflictClause 节点里面至少包含 conflict_target可空和 action两种动作分支两个字段都要有明确的空值语义。EXCLUDED 需要被解析成一个特殊的伪表引用它的列集合和目标表的列集合一致。绑定列时按列名解析而不是按位置。如果只是做语法转换不建议把 ON CONFLICT 简单展开成先 SELECT 再 INSERT/UPDATE的普通语句因为展开后无法保证原子性和并发安全。必须处理返回行数语义DO NOTHING 时冲突行不算成功插入影响行数要按实际插入/更新的行数算。这一点很多做数据库中间件的团队容易忽略。ON CONFLICT 不是普通 INSERT 的语法糖它的执行语义和结果集都不同改写前一定要想清楚。4. 常见问题与排查技巧实录4.1 错误信息速查表说一下我在实际开发里遇到最多的几个报错以及对应的处理办法错误信息出错原因解决办法there is no unique or exclusion constraint matching the ON CONFLICT specification冲突目标和你表上已有的唯一索引匹配不上检查列名拼写、列顺序、是否用了部分唯一索引但没带 WHEREON CONFLICT DO UPDATE command cannot affect row a second time同一条语句里两条待插入数据命中了同一个已存在的冲突行批量数据里先按冲突键去重或者拆成小批次执行invalid reference to FROM-clause entry for table xxxDO UPDATE SET 里错误引用了目标表之外的表更新想要的新值用 EXCLUDED引用当前旧值用目标表名插入行数一直是 0 但没报错这不是 bug是 DO NOTHING 的正常行为想区分插入和忽略用 RETURNING这里面最坑的是第一条。复合唯一键的列顺序和索引定义不一致或者部分唯一索引漏了 WHERE 条件都很容易出现明明有唯一索引但匹配不上的情况。排查的时候别只看错误信息直接查一下 pg_index 和 pg_constraint把索引定义原样抄到 ON CONFLICT 里最稳妥。4.2 性能与并发踩坑记录第一批坑在性能。ON CONFLICT 依赖唯一索引来定位冲突行表上没有对应的唯一约束时语句直接报错索引设计不合理时冲突检测的代价会很高。我在一个千万级的表上做过测试批量写入时一次塞的数据量越大单条语句的锁持有时间就越长建议每批控制在几百到一千行左右而不是一次性灌几万行。第二批坑在更新的副作用。DO UPDATE SET 即使新旧值完全一样也会走完整的更新流程产生新的行版本、触发更新触发器、把 updated_at 改掉。如果每次都拿相同数据来刷表会不断膨胀膨胀率上来了vacuum 都来不及回收。我习惯在更新条件里加一个保护ON CONFLICT (user_id) DO UPDATE SET nickname EXCLUDED.nickname, updated_at now() WHERE user_profile.nickname IS DISTINCT FROM EXCLUDED.nickname;这样只有 nickname 真的变了才会触发更新能显著减少无意义的写放大。注意这里要用 IS DISTINCT FROM而不是因为后者遇到 NULL 会得 UNKNOWN条件永远不成立。第三批坑在序列。即使 DO NOTHING插入尝试也会消耗序列值自增主键会出现明显的断层。这在逻辑上完全正常但如果有人把主键连续当业务约定就会踩坑。建议提前跟团队说清楚用了 ON CONFLICT 的表主键空洞是预期行为不是 bug。还有并发死锁。两个事务分别按不同顺序批量插入同一组 key 时可能形成锁环。比如事务 A 先插入 key1 再插入 key2事务 B 先插入 key2 再插入 key1两者同时在冲突更新阶段锁定了对方需要的行就会死锁。解决办法很粗暴也很有效批量数据先按主键排序所有事务都按同一个顺序处理死锁基本能消除。4.3 我的习惯写法与避坑心得最后分享几个我固定下来的写法。第一只要用了 DO UPDATE SET一定写显式冲突目标不要省略。省略冲突目标只对 DO NOTHING 合法但写清楚目标可以让执行计划稳定也方便后面代码 review 的人一眼看出依赖哪个唯一键。第二复合唯一键的冲突目标我永远保持和索引定义一致的列顺序省去排查的麻烦。第三如果想在一条语句里区分插入和更新可以借助 RETURNING 加一个小技巧。PostgreSQL 里新插入的行 xmax 为 0被更新后的行 xmax 不为 0所以可以这么写INSERT INTO user_profile (user_id, nickname, score) VALUES (1001, 老张, 85) ON CONFLICT (user_id) DO UPDATE SET score EXCLUDED.score RETURNING user_id, (xmax 0) AS inserted;这个写法不是官方文档推荐方案属于经验性的判断某些并发场景下不一定准确但它省事。如果对准确性要求极高我建议在表里加一个created_at字段来做区分插入时用 now()更新时不动它这样只要看 created_at 是否等于 updated_at 就能判断是不是本次新插入的可靠得多。说实话ON CONFLICT 这条语句我刚接触时觉得没什么技术含量就是语法糖。真正在项目里跑起来才发现它把应用层一大票并发处理逻辑简化掉了也让数据同步代码清爽了非常多。如果你的项目还在用先查后插的老套路强烈建议找个低峰期把核心写入路径切到 ON CONFLICT 上体感会很直接。