我最早是在做订单导入的时候被 UPSERT 的坑折磨的。那时候业务每天要从 Excel、外部接口和消息队列往 PostgreSQL 里灌数据，重复提交是家常便饭。最初的代码无非两种：先 SELECT 再决定 INSERT 还是 UPDATE，或者干脆让 INSERT 报一个 duplicate key 的错再补 UPDATE。这两种都能用，但写出来的逻辑又绕又容易出并发问题。后来 PostgreSQL 9.5 引入 INSERT ... ON CONFLICT，也就是大家常说的 UPSERT，业务代码一下子就干净了。这篇内容适合正在跟重复数据较劲的后端、DBA 和数据工程师，核心不是贴语法，而是把冲突目标、EXCLUDED 伪表、DO NOTHING 和 DO UPDATE 的选择，以及并发、序列号、触发器这些实战坑一次说清楚。
1. 从业务痛点说起：为什么需要 UPSERT
1.1 先查询再写入为什么不靠谱
很多团队一开始处理“可能已存在”的数据时，写的是这种三段式逻辑：
-- 常见但不推荐的写法 SELECT id FROM user_profile WHERE user_id = 1001; -- 程序里判断：如果查不到，执行 INSERT -- 如果查到了，执行 UPDATE这看起来没什么问题，但并发场景下非常容易翻车。假设两个事务同时执行 SELECT，都没查到记录，然后都去 INSERT 同一主键或唯一键。其中一个会成功，另一个会在唯一约束上撞车。数据库不会因为你前面查过一次就自动给你加锁，这个“先查后写”的窗口期是天然存在的。用生活里的例子说，就是两个人同时看到停车位空着，都倒车入库，结果必然有一个要撞杆。
当然你也可以在应用层引入分布式锁，或者捕获唯一约束异常再补 UPDATE。但这些方案要么增加中间件依赖，要么让业务代码堆满异常分支。整套流程下来，你会发现真正想让数据正确落库，最靠谱的方式反而最简单：把“判断是否存在”这件事直接交给数据库，让数据库在一条语句内部完成检查、插入、更新三步。
1.2 数据库层的“一次性决策”解决什么问题
INSERT ... ON CONFLICT 让 PostgreSQL 在单条语句内完成冲突判断和动作选择：没有冲突就走普通 INSERT，有冲突就走 DO NOTHING 或者 DO UPDATE。这个操作是原子的，所有判断都在同一事务、同一语句内完成，应用层不再需要“查询-判断-写入”这种容易出竞态的流程。
我在项目里遇到的最大感受是：代码少了，但数据一致性反而高了。以前接口重复请求会触发唯一约束异常，然后前端收到一个 500；现在同样一条语句直接幂等地把数据处理掉，天然抵挡了消息重投、用户重复点击、定时任务重叠这些高频问题。
这里要强调一句：UPSERT 不是银弹，它依赖表上必须有主键、唯一约束或唯一索引。如果你连“什么算重复”都没定义清楚，ON CONFLICT 也无从谈起。这也是后面所有实战细节的地基。
2. UPSERT 核心语法与原理拆解
2.1 一条语句的两个分支
先看最基础的骨架。比如要维护一张商品库存表，按 SKU 覆盖写入库存数：
CREATE TABLE product_stock ( sku VARCHAR(32) PRIMARY KEY, stock INT NOT NULL, updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 核心 UPSERT 语句 INSERT INTO product_stock (sku, stock, updated_at) VALUES ('SKU-001', 120, now()) ON CONFLICT (sku) DO UPDATE SET stock = excluded.stock, updated_at = excluded.updated_at;这段语句执行时，数据库先看SKU-001是否已经存在于表里。不存在就插入新行；存在就让 ON CONFLICT 命中唯一冲突，然后走 DO UPDATE，把库存覆盖成这一次传入的值。
ON CONFLICT后面必须跟两种动作之一：DO NOTHING或DO UPDATE。没有第三种选择。DO NOTHING很好理解，冲突了就直接跳过；DO UPDATE则是当冲突发生时，执行一段类似 UPDATE 的 SET 逻辑。
还有两个新手容易搞混的地方。第一，ON CONFLICT (sku)括号里的列名，必须和表上的唯一约束或唯一索引严格对应，否则 PostgreSQL 直接报错there is no unique or exclusion constraint matching the ON CONFLICT specification。第二，DO UPDATE SET里不能写成普通的SET stock = 120，如果写死常量，那每次冲突都改成同一个值，通常不符合业务预期。要用excluded.stock这种伪表引用，才能拿到这次“被挡住的行”里的数据。
2.2 EXCLUDED 伪表：冲突时我们拿什么值
EXCLUDED 是 UPSERT 里最容易理解错、也最容易用错的概念。它代表“本想要插入、但被排除在外的行”。你可以把它想象成安检口被拦下的那批人：他们已经到了门口，但因为和已有记录冲突，进不了表里。
看下面的例子：
-- 假设表中已有 SKU-001，当前 stock = 50 INSERT INTO product_stock (sku, stock, updated_at) VALUES ('SKU-001', 120, now()) ON CONFLICT (sku) DO UPDATE SET stock = excluded.stock;当冲突发生时，product_stock.stock是表中旧值 50，excluded.stock是本次尝试写入的 120。如果不加前缀直接写stock = excluded.stock，会把库存覆盖成 120。如果你想做累加，就写成：
DO UPDATE SET stock = product_stock.stock + excluded.stock这里product_stock.stock必须带表名或别名，否则 PostgreSQL 在 DO UPDATE 的目标列表里可能会产生歧义。我见过不少同事把 MySQL 的ON DUPLICATE KEY UPDATE习惯带过来，尝试写VALUES(stock)，在 PostgreSQL 里会直接报错。PostgreSQL 只认excluded.列名，这是一种和 MySQL 完全不同的取值方式，建议早点改掉这个习惯。
2.3 两种动作的适用边界
DO NOTHING 和 DO UPDATE 的选择，直接决定业务表现。我把它们的使用场景和注意事项整理成了一张表：
| 动作 | 典型场景 | 冲突时结果 | RETURNING 行为 | 注意点 |
|---|---|---|---|---|
| DO NOTHING | 幂等写入、事件去重、消息重投 | 放弃本次插入，不报错 | 不返回该行 | 要确认是否真的“留空”，不能只依赖它 |
| DO UPDATE | 覆盖更新、累加计数、状态流转 | 更新已有行 | 返回更新后的行 | 必须考虑 excluded 和旧值关系 |
DO NOTHING 最有价值的地方是消息消费者场景。比如订单回调可能被 MQ 重复投递，你希望在订单表里只处理一次。这时直接写：
INSERT INTO order_event (event_no, order_id, payload) VALUES ('EVT-1101', 88001, '{"status":"PAID"}') ON CONFLICT (event_no) DO NOTHING;重复投递时，第二条消息静默跳过。这里的关键点是：DO NOTHING 并不是“把错误吞掉”，而是明确告诉数据库“重复即可，忽略即可”。如果业务需要知道这次到底是插入了还是跳过了，可以配合 RETURNING 判断，这个我在后面排查部分会展开讲。
2.4 冲突目标怎么选：主键、唯一约束还是部分索引
ON CONFLICT 的冲突目标有几种写法，选错的人非常多。最基础的是直接写列名：
ON CONFLICT (id) DO NOTHING;如果唯一性是靠联合唯一约束实现的，就写多个列：
ON CONFLICT (user_id, day) DO UPDATE ...如果约束有名字，也可以直接用ON CONSTRAINT指定：
ON CONFLICT ON CONSTRAINT uniq_user_day DO UPDATE ...还有一个高级写法：部分唯一索引。举个例子，一张主机配置表里，每个主机只允许有一条status = 'active'的配置，但同一个主机可以有多条历史配置。实现它的是部分唯一索引：
CREATE UNIQUE INDEX uniq_host_active ON host_config (host_id) WHERE status = 'active';这种情况下，UPSERT 语句必须把索引谓词也放到冲突目标里：
INSERT INTO host_config (host_id, env, status, config) VALUES (1001, 'prod', 'active', 'new-config') ON CONFLICT (host_id) WHERE status = 'active' DO UPDATE SET config = excluded.config;这个细节非常容易踩坑。如果你只写ON CONFLICT (host_id)，PostgreSQL 会认为你在找一张基于(host_id)的普通唯一约束，而表上只有部分唯一索引，于是直接报no unique or exclusion constraint matching。
还有一点：如果不写冲突目标，直接把ON CONFLICT DO NOTHING放在语句后面，PostgreSQL 会对所有唯一约束生效。但表上有多个唯一约束时，行为可能不太好预期，因为 PostgreSQL 只能选一个约束来处理。所以我的建议是：能用明确的目标列就别用省略写法，尤其是表和索引变复杂之后。
3. 实战场景：从单条写入到批量合并
3.1 场景一：覆盖式写入库存或配置
最直观的 UPSERT 用途，是把“已有则覆盖，没有则插入”的逻辑写成一条语句。我用商品库存同步来演示完整流程。
首先建表：
CREATE TABLE product_stock ( sku VARCHAR(32) PRIMARY KEY, stock INT NOT NULL, updated_at TIMESTAMPTZ NOT NULL DEFAULT now() );然后执行覆盖写入：
INSERT INTO product_stock (sku, stock, updated_at) VALUES ('SKU-001', 120, now()) ON CONFLICT (sku) DO UPDATE SET stock = excluded.stock, updated_at = now();第一次执行时表里没有SKU-001，会插入一行(SKU-001, 120)。第二次执行同样的语句，主键冲突，DO UPDATE 把 stock 覆盖成新的值，同时刷新updated_at。对于定时同步、离线数据导入这种“以最后一次为准”的场景，这条语句基本能解决 90% 的需求。
需要注意的是，如果你只想覆盖业务字段，不要动created_at这类“首次写入时间”字段。因为 UPSERT 冲突后走的逻辑和 UPDATE 类似，如果你在 SET 里没有显式更新它，它就会保留原值，这通常是好事。但如果你的表里没有默认值，又恰好被触发器依赖，可能就会出现一些隐蔽问题，后面我会专门说触发器的坑。
3.2 场景二：计数器累加与初次写入
第二种高频场景是统计类数据，比如页面 PV 计数。表结构用(page_id, day)做联合主键：
CREATE TABLE page_metrics ( page_id BIGINT NOT NULL, day DATE NOT NULL, pv BIGINT NOT NULL DEFAULT 0, uv BIGINT NOT NULL DEFAULT 0, PRIMARY KEY (page_id, day) );写入时只需要传增量，数据库负责在冲突时累加：
INSERT INTO page_metrics (page_id, day, pv, uv) VALUES (2001, CURRENT_DATE, 1, 1) -- 本次访问带来的增量 ON CONFLICT (page_id, day) DO UPDATE SET pv = page_metrics.pv + excluded.pv, uv = page_metrics.uv + excluded.uv;第一次访问某页面某天记录时，没有冲突，直接插入pv=1；后续再来访问，就命中主键冲突，拿旧值加上本次增量写回。这里最舒服的地方是并发安全：两个请求同时INSERT (2001, CURRENT_DATE, 1, 1)时，数据库的行锁会保证第二个请求排队，等第一个提交后再尝试更新，最终pv不会丢累加。
不过要提醒一句：像 UV 这种需要精确去重的指标，不适合用这种uv+excluded.uv的方式，因为多数业务里同一个用户一天内会访问多次，你无法在一条 upsert 里判断他是否已经来过。UV 该用 HyperLogLog、Bitmap 或单独的去重表来算。
3.3 场景三：部分唯一索引下只能有一个“活跃配置”
前面提过的host_config场景，我实际是做发布系统时遇到的。每台主机可以有多份历史配置，但同一时刻只能有一份status='active'的配置。建表加部分唯一索引：
CREATE TABLE host_config ( id BIGSERIAL PRIMARY KEY, host_id INT NOT NULL, env VARCHAR(20) NOT NULL, status VARCHAR(20) NOT NULL, config TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE UNIQUE INDEX uniq_host_active ON host_config (host_id) WHERE status = 'active';部署新配置时，你想把当前激活配置覆盖成新值：
INSERT INTO host_config (host_id, env, status, config) VALUES (1001, 'prod', 'active', 'version-42') ON CONFLICT (host_id) WHERE status = 'active' DO UPDATE SET config = excluded.config;这里有个很隐蔽的行为：部分唯一索引只对status='active'的行生效。如果你插入status='inactive'的历史配置，不管同 host 有没有 active 行，都不会触发唯一冲突，会正常插入历史记录。这是部分索引的天然语义，用之前一定要理解清楚。
我一开始写这段时，毛病出在漏掉了WHERE status = 'active'，报错报得我满头问号。后来反应过来，PostgreSQL 的冲突目标不仅仅要匹配列，还要匹配索引谓词。这是手工建部分唯一索引方案里最常见的错误。
3.4 场景四：批量同步导入的姿势
生产环境很少逐条执行 UPSERT，大部分是批量同步。批量写法并不复杂，多 VALUES 即可：
INSERT INTO product_stock (sku, stock, updated_at) VALUES ('SKU-A', 10, now()), ('SKU-B', 20, now()), ('SKU-C', 30, now()) ON CONFLICT (sku) DO UPDATE SET stock = product_stock.stock + excluded.stock, updated_at = excluded.updated_at;批量模式有两个实际经验。第一，单条 INSERT 一次只处理一条数据，网络往返成本高；多 VALUES 一条语句能减少大量 round-trip，速度提升非常明显，尤其是跨网络连接数据库时。第二，不要盲目追求“一口气全塞”，数据量特别大的时候，单条事务太大不仅占内存，还会长时间持有大量行锁，容易拖垮其他业务。我一般建议每批 500 到 1000 行左右，具体情况按字段宽度和服务器负载调整。
如果批量数据来自另一张表，也可以直接用 INSERT ... SELECT：
INSERT INTO product_stock (sku, stock, updated_at) SELECT sku, import_stock, now() FROM stock_import_tmp ON CONFLICT (sku) DO UPDATE SET stock = product_stock.stock + excluded.stock;这种方式在离线导入和表间合并时非常常用。临时表可以先做去重聚合，再交给 UPSERT，避免出现同一批次里有两行相同主键的问题。关于“同批次重复主键会导致什么”，我放到常见问题部分详细说，那是大部分人批量导入时才遇到的硬坑。
4. 并发安全与性能细节
4.1 并发插入同一行时，数据库内部做了什么
很多同学担心 UPSERT 在并发下会不会抛错或丢数据。我的实际经验是：PostgreSQL 在这里的行为是可靠的，但你需要理解它的等待机制。
假设两个事务同时执行：
INSERT INTO product_stock (sku, stock) VALUES ('SKU-X', 100) ON CONFLICT (sku) DO UPDATE SET stock = product_stock.stock + excluded.stock;如果SKU-X原本不存在，两个事务同时插入时，一个会成功插入，另一个会卡在唯一索引上等待。对方提交后，等待的事务会继续执行 DO UPDATE，把库存累加一次。对方回滚的话，等待的事务则正常插入。整个过程中，应用层不会收到 duplicate key 的错误，数据也不会丢失。
不过并发不是没有代价。多个事务交叉更新同一组行时，可能出现死锁，比如事务 A 更新了行 1 再去更新行 2，事务 B 更新了行 2 再去更新行 1。数据库会检测到死锁并回滚其中一个事务，报40P01 deadlock_detected。对于这种情况，我的建议是：大批量 upsert 时，尽量让数据按同一个顺序处理，比如按主键排序；应用层捕获死锁错误后做有限次数的重试也很有必要。给数据库设置一个合理的lock_timeout也很重要：
SET lock_timeout = '3s';这样可以避免一个事务因为等待另一个长事务而无限挂起。
4.2 序列、自增主键与 ID 空洞
UPSERT 用久了，你一定会发现主键 ID 不是连续的。这不是 bug，而是序列（sequence）的天然行为。别的数据库也有类似问题，但 PostgreSQL 在 UPSERT 场景下会让人格外困惑。
原因在于，PostgreSQL 在执行 INSERT 时，会先计算所有值，包括从序列取nextval。哪怕这一行最终因为冲突被跳过，序列号也已经消耗掉了。举个实际例子：
CREATE TABLE t_order ( id BIGSERIAL PRIMARY KEY, order_no VARCHAR(50) UNIQUE ); -- 第一次插入成功，消耗 id=1 INSERT INTO t_order (order_no) VALUES ('NO-0001'); -- 第二次插入冲突，跳过，但消耗 id=2 INSERT INTO t_order (order_no) VALUES ('NO-0001') ON CONFLICT (order_no) DO NOTHING; -- 第三次插入新订单，id 直接跳到 3 INSERT INTO t_order (order_no) VALUES ('NO-0002');最终表里 id 是 1 和 3，中间缺了 2。如果在执行计划、测试断言里依赖 ID 连续，就会莫名挂掉。要理解的是，ID 序列本来就只保证唯一和递增，不保证连续。业务如果硬性规定流水号必须连续，就不能依赖自增主键，需要单独维护顺序号或使用应用层发号器。
另外，PostgreSQL 15 之后nextval在同一事务里如果被回滚也不会回收，所以哪怕事务最终回滚，序列一样会跳。这一点在批量导入失败后重新执行时尤其明显，ID 可能跳一大截。
4.3 触发器与审计逻辑的隐蔽坑
这是比较容易踩但不查文档很难发现的点。当 UPSERT 在冲突后执行 DO UPDATE 时，PostgreSQL 走的是 UPDATE 行级触发器路径，而不是 INSERT 触发器路径。也就是说，你表上如果挂着BEFORE INSERT触发器准备自动填created_at，或者挂着审计触发器记录“新增数据来源”，冲突场景下这些逻辑不会按你预期执行。
我举一个实际案例。某张表有行级审计触发器，逻辑是：
CREATE TRIGGER trg_order_insert_audit BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION audit_insert();原本这张表只接收普通 INSERT，审计触发器工作正常。后来为了幂等，换成了INSERT ... ON CONFLICT DO UPDATE。结果发现，当记录已存在时，冲突分支更新了数据，但审计表里完全没有记录，因为走的是 UPDATE 触发器，而 UPDATE 触发器当时没有建。排查了很久才确认是触发器执行路径的问题。
更隐蔽的是，如果表上同时有 INSERT 和 UPDATE 触发器，冲突更新会触发 UPDATE 触发器；如果 SET 子句里没有更新某个字段，而这个字段原本是 INSERT 触发器填充的，那么冲突路径拿到的就是一个旧值或空值。所以我建议：凡是 UPSERT 语句里需要保证的字段，不要在 SET 里依赖触发器补值，直接显式赋值最安全。
另外还有一个方向要注意：基于逻辑复制的同步工具，会区分 INSERT 和 UPDATE 事件。UPSERT 冲突时走 DO UPDATE，同步到下游就是 UPDATE 事件，不是 INSERT 事件。如果你的下游在监听 INSERT 做实时处理，这里可能会漏掉新数据，需要提前想清楚。
5. 常见问题与排查技巧实录
5.1 高频报错速查表
这里把我实际遇到过的报错和原因整理成一张表，方便你对照排查：
| 报错信息 | 原因 | 解决办法 |
|---|---|---|
| there is no unique or exclusion constraint matching the ON CONFLICT specification | ON CONFLICT 目标列与唯一约束/唯一索引不匹配，或漏掉了部分索引谓词 | 检查表索引定义，补全列名或索引谓词 |
| ON CONFLICT DO UPDATE command cannot affect row a second time | 同一条 UPSERT 语句里，两行数据冲突到了同一行 | 批量前先去重，或用临时表分组聚合 |
| duplicate key value violates unique constraint | 语句忘写 ON CONFLICT，或冲突类型不受 UPSERT 覆盖 | 补上冲突目标和动作 |
| deadlock detected | 并发事务交叉更新多行 | 统一行处理顺序，应用层捕获重试，设置 lock_timeout |
| 两行 NULL 未触发冲突 | UNIQUE 约束默认把多个 NULL 视为不同值 | PostgreSQL 15+ 用 NULLS NOT DISTINCT 建唯一索引 |
5.2 用 RETURNING 判断到底插入了还是更新了
业务里经常需要知道 UPSERT 到底做了什么，比如判断消息是否被去重处理过。PostgreSQL 里RETURNING可以帮忙，但行为有差异：
- DO NOTHING 跳过时，RETURNING 不会返回任何行。
- DO UPDATE 和普通插入时，RETURNING 会返回受影响的行。
如果你想统计“这一批里有多少条真正插入、多少条重复跳过”，可以这样写：
WITH inserted AS ( INSERT INTO order_event (event_no, order_id, payload) VALUES ('EVT-1101', 88001, '{"status":"PAID"}') ON CONFLICT (event_no) DO NOTHING RETURNING event_no ) SELECT count(*) AS inserted_count FROM inserted;如果返回0，说明事件已经存在，本次是重复消息；如果返回1，说明是首次写入。这个技巧在处理 MQ 消费幂等时非常实用。
如果想知道某条语句走了哪个执行路径，可以用 EXPLAIN：
EXPLAIN (ANALYZE, BUFFERS) INSERT INTO product_stock (sku, stock) VALUES ('SKU-A', 100) ON CONFLICT (sku) DO UPDATE SET stock = excluded.stock;执行计划里会看到Insert on product_stock节点，以及Conflict ArbiterIndexes等信息。通过观察rows和Buffers可以判断插入是否触发过磁盘读写。大部分情况下肉眼已经足够判断，不需要过度追求执行计划分析，但作为定位手段仍然值得掌握。
5.3 回归测试与版本兼容性
我建议每个用到 UPSERT 的模块都留一个很小的烟雾测试，避免后续改表结构、改索引时静默出问题。最简单的做法是建一张临时表，连续执行两次相同插入：
CREATE TEMP TABLE upsert_smoke ( id INT PRIMARY KEY, cnt INT NOT NULL DEFAULT 0 ); -- 第一次：应该插入 INSERT INTO upsert_smoke (id, cnt) VALUES (1, 1) ON CONFLICT (id) DO UPDATE SET cnt = upsert_smoke.cnt + 1; -- 第二次：应该累加，而不是新增 INSERT INTO upsert_smoke (id, cnt) VALUES (1, 1) ON CONFLICT (id) DO UPDATE SET cnt = upsert_smoke.cnt + 1; -- 断言：只有一行，且 cnt = 2 SELECT cnt = 2 AS ok FROM upsert_smoke WHERE id = 1;这套脚本应该放进 CI 或发布前检查里，尤其是修改过表约束、索引或触发器之后。
另一个很多人容易忽略的点是版本。INSERT ... ON CONFLICT 是 PostgreSQL 9.5 才引入的特性，如果你的数据库还停留在 9.4，UPSERT 在语法层面就不存在，只能退回到“先查后写”或者用暂存表合并。新项目建议直接选择维护中的大版本，老库改造前先确认SHOW server_version;。PostgreSQL 15 之后提供了标准 SQL 的 MERGE 语句，也能实现类似效果，但那是另一套语义，而且不是所有场景都更适合，我这里不展开，至少你心里要有这个选项。
最后说一点个人体会：我不太建议把很重的业务规则全塞进一条几十行的 UPSERT 语句——虽然它本事不小，但可读性和排错成本都很高。它最擅长的场景，是把“重复写入”变成“一次原子决策”；至于复杂的合并策略，完全可以在应用层先算好，再把结果交给 ON CONFLICT 去落地。这个思路至少帮我少踩了很多坑。
