最近在处理一批历史遗留ETL脚本时，"先查再插"和"删了再插"的逻辑几乎成了标配，代码冗长不说，高并发下还经常出幺蛾子。后来我把它们统一改成了INSERT ... ON CONFLICT ... DO (UPDATE SET ...)/(NOTHING)的写法，也就是大家常说的 UPSERT，单条SQL就把"存在则更新、不存在则插入、重复则跳过"这几件事全部收掉。这篇不讲空话，直接按我在生产环境里的使用顺序，把 ON CONFLICT 的语法、实战、冲突目标、并发性能和一串踩坑记录完整过一遍。适合刚接触 PostgreSQL 的读者，也适合正在优化数据同步和写入逻辑的老手。
1. 为什么非要用 ON CONFLICT：三种传统UPSERT写法的硬伤
1.1 "先查再插"的竞态漏洞
很多项目里最自然的写法是：先 SELECT 一下，判断记录存不存在，不存在就 INSERT，存在就 UPDATE。逻辑上看没毛病，但一旦上了并发，问题立刻暴露。两个会话同时 SELECT，发现记录都不存在，然后同时执行 INSERT，其中一个必然撞上唯一约束冲突。应用侧如果没 catch 住这个异常，整个批任务直接中断。
我见过有人在这种写法外面套一层"冲突后重试"，但重试逻辑很容易写歪。更麻烦的是，三个 SQL 的往返延迟在批量场景下是不可忽视的，真实的实时数据链路每秒几万写入时，这种写法基本撑不住。还有一种更激进的"DELETE 再 INSERT"，直接用删除规避冲突判断，但这带来了外键引用、UPDATE 触发器、自增 ID 变化等一系列连锁问题，我通常不建议碰。
1.2 数据库原生能力登场：ON CONFLICT 到底做了什么
PostgreSQL 从 9.5 开始引入ON CONFLICT，核心思想是把"检测冲突"和"处理冲突"下沉到索引检查层面。插入时，数据库先在唯一索引上探测，如果发现要插入的行已经和某条唯一键匹配，就走后面的冲突处理分支；没有冲突，就正常插入。整个检测是原子的，不依赖应用层的"先查一下"再去"猜"，从根上消除了竞态窗口。
它像在门口装了闸机，而不是让两个人先跑到窗口问还有没有位置，再回来取钱买票——竞态天然消失。MySQL 有ON DUPLICATE KEY UPDATE，SQL Server 和 Oracle 有MERGE，PostgreSQL 的ON CONFLICT在语义清晰度和执行效率上都有自己很鲜明的特点。完整语法长这样：
INSERT INTO table_name [AS alias] (column1, column2, ...) VALUES (...) ON CONFLICT conflict_target conflict_action;其中conflict_target可以写列名列表、约束名或者带 WHERE 的部分索引；conflict_action则是两种：DO NOTHING和DO UPDATE SET ...。语法本身不复杂，真正的复杂度都在后面的分支选择、冲突目标匹配和生产环境里的边界情况上。
2. 两种分支怎么选：DO NOTHING和DO UPDATE SET
2.1 执行语义和锁行为的差异
从语义上看，DO NOTHING是发现冲突之后什么都不做，直接跳过这一行；DO UPDATE SET是发现冲突后，把当前已存在的行按你指定的表达式更新一遍。但真正影响线上表现的是锁行为。
DO NOTHING在判定冲突后，不会对已存在行加更新锁，它只需要等待冲突事务提交确认即可。DO UPDATE SET则必须拿到目标行的行锁，然后执行更新，所以如果多个并发事务反复更新同一行，锁等待和排队是必然的。我自己的经验是：核心诉求只是"不重复写入"（幂等）时，优先DO NOTHING，少拿锁就是少排队，也是给数据库减负。
2.2 EXCLUDED伪表：拿到"被堵回来的那行数据"
EXCLUDED伪表是 ON CONFLICT 里最精华的部分。它代表"这些行本来打算插入，但因为冲突被当前唯一约束拦下来的数据"，本质是 INSERT 子句里提供的那一行值。在DO UPDATE SET中，通过EXCLUDED.column就能引用这一行的值。
INSERT INTO counters (user_id, cnt) VALUES (1001, 1) ON CONFLICT (user_id) DO UPDATE SET cnt = counters.cnt + EXCLUDED.cnt;当冲突发生时，counters.cnt是当前库里已有的值，EXCLUDED.cnt是这次尝试插入的值。有了这个机制，累加操作就变得非常简单。没有EXCLUDED的话，你根本拿不到"刚才想插入的那份数据"，只能绕到 VALUES 构造或者子查询里，写起来非常痛苦。注意EXCLUDED只在DO UPDATE SET分支里有意义，DO NOTHING分支里引用它没有任何效果。
2.3 DO UPDATE SET 后加WHERE，过滤无效更新
很多刚接触 ON CONFLICT 的人不知道DO UPDATE SET后面还能跟 WHERE。这个 WHERE 的作用是：冲突发生时，如果条件不成立，就放弃更新，相当于本次"处理但不做事"。它和普通 UPDATE 的 WHERE 一样，可以读取当前已存在行的列。典型场景是带版本号的同步：
INSERT INTO sync_items (item_id, val, version) VALUES (42, 'new value', 3) ON CONFLICT (item_id) DO UPDATE SET val = EXCLUDED.val, version = EXCLUDED.version WHERE sync_items.version < EXCLUDED.version;sync_items.version < EXCLUDED.version保证了新版本才能覆盖旧版本，避免旧数据把新数据冲掉。这个特性在做乐观锁、消息驱动的状态同步时非常实用。我见过不少开发者遇到"只在源端数据版本更高时更新"的需求，最后绕远路去写多条 SQL 还要自己加锁，其实数据库原生支持。
3. 实战案例：从幂等写入到批量合并
3.1 用户行为日志的去重写入
先看最典型的幂等场景。用户行为日志，消息队列可能重复投递，应用侧希望同一事件只记录一次：
CREATE TABLE user_events ( user_id bigint, event_id uuid, event_type text, payload jsonb, occurred_at timestamptz, PRIMARY KEY (user_id, event_id) );写入时直接：
INSERT INTO user_events (user_id, event_id, event_type, payload, occurred_at) VALUES (1001, '5f0b9e8c-...', 'click', '{"page": "/home"}', now()) ON CONFLICT (user_id, event_id) DO NOTHING;重复事件直接跳过，不影响主流程。这个写法在 MQ 消费者里尤其合适，配合 Producer 端生成 UUID 作为 event_id，天然幂等。消费者崩溃后重投、重试，也不用在应用层做任何去重判断。如果你现在还在消费者里用 Redis Set 或者数据库先查一遍做去重，换成这个会更干净。
3.2 计数器累加与最新时间刷新
第二个常见需求是计数器累加。比如用户维度的事件统计表：
CREATE TABLE user_counts ( user_id bigint PRIMARY KEY, cnt int NOT NULL DEFAULT 0, latest_at timestamptz NOT NULL DEFAULT now() );实时累加一次：
INSERT INTO user_counts (user_id, cnt, latest_at) VALUES (1001, 1, now()) ON CONFLICT (user_id) DO UPDATE SET cnt = user_counts.cnt + EXCLUDED.cnt, latest_at = EXCLUDED.latest_at;这一条 SQL 同时完成"首次插入 + 后续原子累加 + 刷新最近时间"，比先查后 update 性能高不少。cnt = user_counts.cnt + EXCLUDED.cnt这一行是整个操作的核心：库里已有的值加上本次尝试插入的值。传多行时也能玩出更多花样，比如批量传一个数组进来，一次累加多个用户的计数。
3.3 批量导入：unnest数组展开 + ON CONFLICT联合使用
第三个是批量合并场景。比如商品表，每天从上游同步一次价格和库存，存在就更新，不存在就插入：
INSERT INTO products (sku, name, price, stock, updated_at) SELECT * FROM unnest( $1::text[], $2::text[], $3::numeric[], $4::int[], $5::timestamptz[] ) ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price, stock = products.stock + EXCLUDED.stock, updated_at = EXCLUDED.updated_at WHERE products.price <> EXCLUDED.price OR products.stock <> EXCLUDED.stock;这里有几个细节：unnest把参数数组展开成多行，一次处理几千条完全没问题；UPDATE 部分用了products.stock + EXCLUDED.stock，表示库存累加而不是直接覆盖，这取决于业务定义，也可以直接赋值；最后那个 WHERE 非常关键，它让"价格和库存都没变化"的行不用生成新行版本。
提个醒：数据库里更新一行会生成新的行版本，MVCC 机制下这些旧版本不是马上消失。没有必要的写操作，会造成 WAL 放大和表膨胀，尤其在频繁同步的场景里很致命。加上这个 WHERE，同步任务只在数据真正变化时才去写磁盘。
另外RETURNING的语义也要记住：INSERT ... ON CONFLICT DO UPDATE ... RETURNING id会把"实际插入的行"和"实际更新的行"都返回来，但DO NOTHING跳过的行不会出现在结果里。我做同步任务时经常用它回传"本次影响的行数"，用来统计同步率或触发下一步流程。
4. 冲突目标选取：唯一索引、部分索引和约束名
4.1 不指定冲突目标的报错与规则
ON CONFLICT后面的括号里写的叫冲突目标。如果表上只有一个唯一约束，你甚至可以简化成ON CONFLICT DO NOTHING，不写具体列，数据库知道去查哪个索引。但表上有多个唯一约束时，你不指定，PostgreSQL 就会直接报错：
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time等等，这个报错其实是批内重复数据导致的，另一个更常见的报错是：
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification后者多半是你指定的冲突列名和表上实际唯一约束对不上。比如ON CONFLICT (email)，但email只有普通索引没有唯一索引，数据库根本不知道拿什么来判定冲突。所以写之前要先把表上的唯一索引理清楚，到底是对哪一列、哪一组列。
4.2 部分唯一索引下的ON CONFLICT
很多业务里的唯一性是有条件的。比如用户邮箱，只对"未删除用户"唯一，被软删掉的用户邮箱应该允许被别人注册：
CREATE UNIQUE INDEX uq_users_email_not_deleted ON users(email) WHERE deleted_at IS NULL;写入时：
INSERT INTO users (email, name, deleted_at) VALUES ('alice@example.com', 'Alice', NULL) ON CONFLICT (email) WHERE deleted_at IS NULL DO UPDATE SET name = EXCLUDED.name;这里ON CONFLICT后面的(email) WHERE deleted_at IS NULL，要和部分唯一索引的谓词完全一致。部分索引的精髓是"只对满足条件的数据生效"，所以软删除的邮箱不会拦住新用户注册。这个技巧在需要"带条件的唯一约束"场景下非常强大，也是普通 SQL 教程里讲得比较少的部分。
4.3 用约束名指定冲突目标
除了列名列表，ON CONFLICT 还支持直接用约束名：
INSERT INTO products (sku, name, price, stock) VALUES ('SKU-001', '商品A', 19.9, 100) ON CONFLICT ON CONSTRAINT products_sku_key DO UPDATE SET price = EXCLUDED.price;这种写法更显式，适合约束名本身就表达语义的场景。但实际工作中我更习惯写列名列表，因为可读性更高，而且如果约束后面被删掉重建，列名写法通常还能继续工作，约束名写法就要去核对新名字。当然，如果你的唯一性是建立在表达式索引上，用约束名有时反而更省事。
5. 并发和性能：线上环境实测记录
5.1 与"先查后插"的压测对比
我自己用 pgbench 对一张100万行的表做过简单压测，场景是模拟"存在则更新"的混合负载，10 个并发客户端，每个客户端执行 1 万次操作。结果大致如下：
| 方案 | 相对耗时 | 说明 |
|---|---|---|
| 先查后插（SELECT + UPDATE/INSERT） | 基准（最慢） | 三次 SQL 往返，索引查找做两遍，锁持有时间不连续 |
| ON CONFLICT DO UPDATE | 约为基准的 1/2 到 2/3 | 单条 SQL 合并，执行计划更紧凑 |
| ON CONFLICT DO NOTHING | 低冲突率下接近普通 INSERT | 冲突时什么都不做，几乎没有额外开销 |
这个结论和我的生产环境观察一致。性能差距主要来自三块：网络往返次数从 2-3 次降到 1 次；索引查找从两次降到一次；锁持有时间更加集中，减少了等待窗口。特别是低冲突率场景下DO NOTHING很快，因为它发现冲突后几乎什么都不做。
5.2 无法同时修改两次的报错：批内重复数据怎么处理
ON CONFLICT 有一个非常经典的限制。看这段：
INSERT INTO t (id, val) SELECT * FROM (VALUES (1, 10), (1, 20)) AS v(id, val) ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;执行后你会看到：
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time意思是：一条 INSERT 语句里有两行都命中同一个唯一键id=1，第一行插入后，第二行去更新它；如果再出现第三行，仍然要更新同一行——PostgreSQL 不允许在同一条语句里对同一行连续修改两次。这不是 bug，是设计约束。
解决思路有三种：
- 上游先
GROUP BY去重，只保留同一个唯一键的一条数据； - 拆成多批提交，控制批内不出现重复目标行；
- 先加载到临时表，用聚合函数挑出"最后一条"，再交给 ON CONFLICT。
我做数据同步时的习惯是：在临时表里先SELECT DISTINCT ON (id) ... ORDER BY id, occurred_at DESC取最新状态，然后再INSERT ... ON CONFLICT，从根上绕开这个限制。
5.3 锁等待、死锁与批量顺序
DO UPDATE SET要获取目标行锁，多个并发事务按不同顺序更新同一组行时，就可能死锁。比如事务 A 先更新行 1 再更新行 2，事务 B 先更新行 2 再更新行 1，时间凑巧的话就互相等对方持有的锁，数据库检测到死锁后回滚其中一个事务。
预防的核心是固定更新顺序。批量写入时先按主键或唯一键排序，保证所有会话都以相同顺序拿锁，死锁概率会大幅下降。另外单批行数不要过大，减少单事务的锁持有总时长。关键任务还可以在会话级别把lock_timeout设小一点，比如 300ms，锁等待超时直接失败，然后在上层做重试，这比卡在数据库里半天不动强得多。死锁检测虽然默认开着，但死锁回滚带来的业务报错还是越少越好。
6. 让我印象深刻的几个坑
6.1 DO UPDATE触发的是UPDATE触发器，DO NOTHING不触发
这是很多人踩完才明白的点。ON CONFLICT DO UPDATE SET走的路径就是标准 UPDATE，BEFORE UPDATE / AFTER UPDATE触发器都会触发；而DO NOTHING则什么触发器都不触发。换句话说，你不能指望"没 insert 就不该触发 update 触发器"这种判断成立——只要走到了 DO UPDATE 分支，它就是一个地地道道的 UPDATE 操作。
在实际业务里，如果表上有"最后修改时间由触发器自动维护"的逻辑，ON CONFLICT DO UPDATE 会按预期触发；但如果表里有统计"插入次数"的触发器，可能会被重复触发，造成计数膨胀。反过来，你想在"冲突跳过"时也给某个字段打个标记，DO NOTHING 是不会帮你做这些旁路动作的，必须自己去别的地方处理。
6.2 被白白消耗的序列号
如果表主键是serial或IDENTITY自增列，ON CONFLICT DO NOTHING 冲突跳过的行，同样已经把nextval调用了。序列号不会回退，所以你会看到表主键出现空洞，偶尔缺号。这个现象在 MySQL 的auto_increment上也有，但很多业务同学会把主键空洞误认为数据丢失，跑过来找你排查。
如果你真的需要严格连续编号，数据库层用序列是不行的，只有应用层统一分配或加锁串行插入。多数场景下主键空洞不影响逻辑，只要保证唯一性就行，不用太焦虑。但如果你在做数据对账，最好提前告诉同事"这个表主键会有空洞"，省得人家大半夜发消息问你。
6.3 ON CONFLICT和MERGE的选型，别纠结太久
PostgreSQL 15 引入了 SQL 标准的 MERGE，可以一条语句做 INSERT、UPDATE、DELETE 多种动作，看起来比 ON CONFLICT 更全能。但 MERGE 的语法更重，执行计划在简单 UPSERT 场景里不一定比 ON CONFLICT 更快，早期版本还有并发 bug 的记录。我的经验是：纯"插入或更新/跳过"场景，一律 ON CONFLICT；真的需要在一条语句里做多分支操作，再考虑 MERGE。
如果一个团队里大多数人还不太熟 MERGE 语义，我宁愿拆成几条清晰 SQL，也不愿意留一条复杂 MERGE 变成后续维护的负担。ON CONFLICT 加上 EXCLUDED 和 WHERE，覆盖了绝大多数业务场景，先用好它比盲目追新更重要。
6.4 SET子句里的子查询可能拖垮批量导入
最后说一个性能大坑：有人喜欢在DO UPDATE SET里写标量子查询，比如：
ON CONFLICT (id) DO UPDATE SET remark = (SELECT note FROM source_data WHERE source_data.id = products.id)看起来没毛病，但这条子查询会对每一行冲突数据执行一次。批量几万行时，每行都去查另一张表，累积延迟非常恐怖。即使有索引，几万次索引探测也会把导入速度拖到无法忍受。正确做法是先在 CTE 或临时表里把映射关系准备好，再通过 JOIN 方式取数据，或者一次性用unnest把需要的字段全部带过来，避免反复查询。
这套 ON CONFLICT 语法我用了好几年，最大的感受是它把数据写入里最繁琐的"防重 + 更新"这一层下沉到了数据库内核，应用层代码干净了一大截。如果你还在用"先查再插"，建议找个下午把手头的写入逻辑梳理一遍，能从 DO NOTHING 开始的都换掉，再逐步体验 EXCLUDED 和 WHERE 带来的控制力。那些靠临时打补丁支撑的数据代码，大多可以顺手删掉了。
