我最早是在做订单导入的时候被 UPSERT 的坑折磨的。那时候业务每天要从 Excel、外部接口和消息队列往 PostgreSQL 里灌数据重复提交是家常便饭。最初的代码无非两种先 SELECT 再决定 INSERT 还是 UPDATE或者干脆让 INSERT 报一个 duplicate key 的错再补 UPDATE。这两种都能用但写出来的逻辑又绕又容易出并发问题。后来 PostgreSQL 9.5 引入 INSERT ... ON CONFLICT也就是大家常说的 UPSERT业务代码一下子就干净了。这篇内容适合正在跟重复数据较劲的后端、DBA 和数据工程师核心不是贴语法而是把冲突目标、EXCLUDED 伪表、DO NOTHING 和 DO UPDATE 的选择以及并发、序列号、触发器这些实战坑一次说清楚。1. 从业务痛点说起为什么需要 UPSERT1.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是表中旧值 50excluded.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;第一次访问某页面某天记录时没有冲突直接插入pv1后续再来访问就命中主键冲突拿旧值加上本次增量写回。这里最舒服的地方是并发安全两个请求同时INSERT (2001, CURRENT_DATE, 1, 1)时数据库的行锁会保证第二个请求排队等第一个提交后再尝试更新最终pv不会丢累加。不过要提醒一句像 UV 这种需要精确去重的指标不适合用这种uvexcluded.uv的方式因为多数业务里同一个用户一天内会访问多次你无法在一条 upsert 里判断他是否已经来过。UV 该用 HyperLogLog、Bitmap 或单独的去重表来算。3.3 场景三部分唯一索引下只能有一个“活跃配置”前面提过的host_config场景我实际是做发布系统时遇到的。每台主机可以有多份历史配置但同一时刻只能有一份statusactive的配置。建表加部分唯一索引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;这里有个很隐蔽的行为部分唯一索引只对statusactive的行生效。如果你插入statusinactive的历史配置不管同 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 ... SELECTINSERT 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 ); -- 第一次插入成功消耗 id1 INSERT INTO t_order (order_no) VALUES (NO-0001); -- 第二次插入冲突跳过但消耗 id2 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 specificationON 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 消费幂等时非常实用。如果想知道某条语句走了哪个执行路径可以用 EXPLAINEXPLAIN (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.4UPSERT 在语法层面就不存在只能退回到“先查后写”或者用暂存表合并。新项目建议直接选择维护中的大版本老库改造前先确认SHOW server_version;。PostgreSQL 15 之后提供了标准 SQL 的 MERGE 语句也能实现类似效果但那是另一套语义而且不是所有场景都更适合我这里不展开至少你心里要有这个选项。最后说一点个人体会我不太建议把很重的业务规则全塞进一条几十行的 UPSERT 语句——虽然它本事不小但可读性和排错成本都很高。它最擅长的场景是把“重复写入”变成“一次原子决策”至于复杂的合并策略完全可以在应用层先算好再把结果交给 ON CONFLICT 去落地。这个思路至少帮我少踩了很多坑。