原文地址https://www.cybertec-postgresql.com/en/insert-on-conflict-do-select-a-new-feature-in-postgresql-v19/INSERT … ON CONFLICT … DO SELECT: PostgreSQL v19 的新特性作者Laurenz Albe时间2026年3月分类操作指南新闻标签开发SQL 帮助新特性一个银行劫匪举着牌子上面写着错误的 SQL 语法造成的可怕后果“INSERT INTO my_account VALUES (10000) ON CONFLICT (have_no_money) DO SELECT all_money FROM bank;”© Laurenz Albe 2026目录什么是 INSERT … ON CONFLICT一个展示 MATCH 存在竞态条件的示例新特性 ON CONFLICT … DO SELECT 的描述ON CONFLICT … DO SELECT 的使用场景在旧版 PostgreSQL 中绕过缺乏 ON CONFLICT … DO SELECT 的方法为什么仍然没有 ON CONFLICT … DO DELETE结论PostgreSQL 从 9.5 版本开始就支持INSERT语句的非标准ON CONFLICT子句。在 v19 版本中通过提交 88327092ff 增加了ON CONFLICT ... DO SELECT。借此机会我们重新审视一下ON CONFLICT的好处并看看新的DO SELECT变体能带来哪些便利什么是 INSERT … ON CONFLICTINSERT ... ON CONFLICT是 PostgreSQL 对所谓 “upsert”插入或更新操作的实现您想向表中插入数据但如果表中已存在冲突的行您希望要么保留现有行不变要么转而更新该行。前者可以通过使用ON CONFLICT DO NOTHING实现。要更新冲突的行则使用ON CONFLICT ... DO UPDATE SET ...。请注意在使用后一种语法时必须指定一个冲突目标可以是一个约束或一个唯一索引PostgreSQL 会根据这个目标来检测冲突。您可能会疑惑为什么 PostgreSQL 要为这个 upsert 操作提供专门的语法。毕竟SQL 标准已经有一个MERGE语句似乎涵盖了相同的功能。确实PostgreSQL 直到 v15 才支持MERGE但这几乎不足以成为引入新的非标准语法的理由。真正的原因是INSERT ... ON CONFLICT与MERGE不同它不存在竞态条件即使在并发数据修改的情况下INSERT ... ON CONFLICT ... DO UPDATE也能保证要么执行INSERT要么执行UPDATE。它不会因为——比如说——在我们尝试插入和更新某行之间一个并发事务删除了冲突行而导致失败。一个展示 MATCH 存在竞态条件的示例创建如下表CREATETABLEtab(keyintegerPRIMARYKEY,valueinteger);然后开启一个事务并插入一行BEGIN;INSERTINTOtabVALUES(1,1);在另一个并发会话中运行一个MERGE语句MERGEINTOtabUSING(SELECT1ASkey,2ASvalue)ASsourceONsource.keytab.keyWHENMATCHEDTHENUPDATESETvaluesource.valueWHENNOTMATCHEDTHENINSERTVALUES(source.key,source.value);这个MERGE语句将会挂起。在您提交插入行的事务之后MERGE将引发一个错误ERROR:duplicatekeyvalueviolatesuniqueconstrainttab_pkeyDETAIL:Key(key)(1)alreadyexists.而相应的INSERT ... ON CONFLICT ... DO UPDATE语句则能正确地更新新行INSERTINTOtabVALUES(1,2)ONCONFLICT(key)DOUPDATESETvalueEXCLUDED.value;新特性 ON CONFLICT … DO SELECT 的描述虽然INSERT 或 UPDATE对大多数人来说很容易理解但INSERT 或 SELECT乍听起来可能令人困惑提取数据怎么能替代插入数据呢实际上这个新子句只能与INSERT ... RETURNING结合使用。此外v19 新子句的语法是DOSELECT[FOR{UPDATE|NOKEYUPDATE|SHARE|KEYSHARE}][WHEREcondition]因此虽然可以选择对从表中选中的行加行锁但不能指定列列表。如果再仔细想想这其实是有道理的。毕竟一个 SQL 语句的所有结果行必须具有相同的结构所以 PostgreSQL 会直接使用RETURNING列表作为该语句的SELECT列表。ON CONFLICT … DO SELECT 的使用场景我认为只要INSERT ... RETURNING有用的地方这个新子句就有用武之地比如表中包含由生成列或触发器填充的列或者使用了会对插入数据进行四舍五入或截断的数据类型。一般来说INSERT ... RETURNING省去了您再次查询刚插入的行以确定数据库实际存储了什么值的麻烦。举一个简单的例子考虑一个人员表它包含一个生成的主键、一个奥地利社会保障号唯一和一个姓名CREATETABLEperson(idbigintGENERATED ALWAYSASIDENTITYPRIMARYKEY,svnrvarchar(10)UNIQUENOTNULL,nametextNOTNULL);如果您想知道序列为插入的行生成了哪个id可以使用INSERTINTOperson(svnr,name)VALUES(1750201068,Laurenz)RETURNINGid;如果您尝试插入表中的数据可能已经存在于表中您可以通过以下方式忽略重复行INSERTINTOperson(svnr,name)VALUES(1750201068,Laurenz),(1053080982,mary)ONCONFLICT(svnr)DONOTHING;您也可以将ON CONFLICT DO NOTHING与RETURNING结合使用但这样语句只会返回新插入行的id。如果您需要那些您尝试插入但表中已存在的行的id您要么需要额外运行一个SELECT语句要么可以使用新的ON CONFLICT ... DO SELECTINSERTINTOperson(svnr,name)VALUES(1750201068,Laurenz),(1053080982,mary)ONCONFLICT(svnr)DOSELECTRETURNINGid;在旧版 PostgreSQL 中绕过缺乏 ON CONFLICT … DO SELECT 的方法如果您不想为了检索缺失的行而执行第二条语句有一种方法可以使用ON CONFLICT ... DO UPDATE达到相同的结果INSERTINTOperson(svnr,name)VALUES(1750201068,Laurenz),(1053080982,mary)ONCONFLICT(svnr)DOUPDATESETnameperson.nameRETURNINGid;但是虽然这种不做任何更改的UPDATE会返回正确的结果但它有明显的缺点每个UPDATE都会产生一个死元组后续需要VACUUM来回收空间除非是 HOT仅堆元组更新否则 PostgreSQL 必须修改所有索引即使所有数据保持不变这对性能不利。为什么仍然没有 ON CONFLICT … DO DELETE您是在开玩笑吧对吗这样的子句能有什么可能的用途呢如果您在寻找一个仍然缺失且用户反复期望的特性那应该是在ON CONFLICT子句中指定多个仲裁约束的选项。结论ON CONFLICT ... DO SELECT子句为已经非常有用的INSERT ... ON CONFLICT语句增加了一个曾经缺失的特性。它在某些列由数据库生成或触发器修改原始数据的情况下最为有用。[文件内容结束]