DeepSeek总结的PostgreSQL v19 新特性:INSERT ... ON CONFLICT ... DO SELECT
原文地址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语句增加了一个曾经缺失的特性。它在某些列由数据库生成或触发器修改原始数据的情况下最为有用。[文件内容结束]

相关新闻

工业防爆小型气象站

工业防爆小型气象站

临时防爆作业区、小型矿山、加油站、危险品临时存储点等场景,具有点位分散、部署周期灵活、环境复杂且易燃易爆风险突出的特点,对气象监测设备的便携性、防爆安全性、低功耗及部署效率提出特殊要求。小型防爆气象站精准适配此类需求,以便携部…

2026/7/6 5:34:56 阅读更多 →
含风光柴储微网多目标优化调度:MATLAB代码实现与探索

含风光柴储微网多目标优化调度:MATLAB代码实现与探索

MATLAB代码:含风光柴储微网多目标优化调度 关键词:微网调度 风光柴储 粒子群算法 多目标优化 参考文档:《基于多目标粒子群算法的微电网优化调度》 仿真平台:MATLAB 平台采用粒子群实现求解 主要内容:代码构建了含风机…

2026/5/17 8:21:10 阅读更多 →
如何通过数字员工与熊猫智汇实现呼叫中心的高效运作?

如何通过数字员工与熊猫智汇实现呼叫中心的高效运作?

数字员工通过智能化的工作流程,显著优化了企业的业务运作。以AI销冠系统为基础,数字员工能够实现高效的客户外呼,以最低的人力成本,完成大量的客户联系任务。这种智能解决方案,被广泛应用于呼叫中心,使每位…

2026/7/6 5:08:07 阅读更多 →

最新新闻

Wand-Enhancer:开源增强工具让游戏修改体验全面升级

Wand-Enhancer:开源增强工具让游戏修改体验全面升级

Wand-Enhancer:开源增强工具让游戏修改体验全面升级 【免费下载链接】Wand-Enhancer Advanced UX and interoperability extension for Wand (WeMod) app 项目地址: https://gitcode.com/gh_mirrors/we/Wand-Enhancer Wand-Enhancer是一款专为Wand&#xff0…

2026/7/6 6:34:56 阅读更多 →
5步掌握AMD Ryzen调试工具:从新手到硬件掌控者

5步掌握AMD Ryzen调试工具:从新手到硬件掌控者

5步掌握AMD Ryzen调试工具:从新手到硬件掌控者 【免费下载链接】SMUDebugTool A dedicated tool to help write/read various parameters of Ryzen-based systems, such as manual overclock, SMU, PCI, CPUID, MSR and Power Table. 项目地址: https://gitcode.c…

2026/7/6 6:34:56 阅读更多 →
Claude Code砍80%提示词:AI降本从拆Prompt债

Claude Code砍80%提示词:AI降本从拆Prompt债

Anthropic 前两天做了一件反直觉的事——删掉了 Claude Code 80% 的 system prompt。从 65K tokens 砍到 13K 左右,表现反而更好。 你可能也注意到了:AI 编程工具跑了一年多,各家 agent 的 system prompt 从几百行膨胀到几千行。但 Anthropic…

2026/7/6 6:32:56 阅读更多 →
1.6.4打破一切MITE

1.6.4打破一切MITE

1.6.4MITE太好玩了

2026/7/6 6:30:55 阅读更多 →
如何通过线上线下结合的旅行社模式,提升竞争力?张源知

如何通过线上线下结合的旅行社模式,提升竞争力?张源知

线上线下结合的旅行社模式日益受到关注、尤其是在消费者对旅行体验要求越来越高的背景下。利用这一模式、旅行社能够同时利用线上平台的便利和线下服务等亲切感,这样更好地满足客户的需求。随着技术不断进步,数字化工具提供了更智能的运营方式&#xff0…

2026/7/6 6:28:55 阅读更多 →
ICM-42688-P与STM32F405ZG在运动感知系统中的应用

ICM-42688-P与STM32F405ZG在运动感知系统中的应用

1. ICM-42688-P与STM32F405ZG的黄金组合解析在工业自动化和机器人控制领域,精确的运动感知能力往往决定着整个系统的性能上限。ICM-42688-P作为TDK InvenSense推出的6轴MEMS惯性测量单元(IMU),与STMicroelectronics的STM32F405ZG微控制器形成的技术组合&…

2026/7/6 6:28:55 阅读更多 →

日新闻

H2 与 MySQL 单元测试兼容性:5 个关键 SQL 语句差异与规避方案

H2 与 MySQL 单元测试兼容性:5 个关键 SQL 语句差异与规避方案

H2与MySQL单元测试兼容性:5个关键SQL语句差异与规避方案1. 单元测试中的数据库兼容性挑战在Java开发领域,单元测试是保证代码质量的重要环节。当应用涉及数据库操作时,测试环境的搭建往往成为开发者的痛点。H2数据库因其轻量级、内存模式和快…

2026/7/6 0:01:17 阅读更多 →
Windows任务栏终极清理指南:用RBTray一键隐藏窗口到系统托盘

Windows任务栏终极清理指南:用RBTray一键隐藏窗口到系统托盘

Windows任务栏终极清理指南:用RBTray一键隐藏窗口到系统托盘 【免费下载链接】rbtray A fork of RBTray from http://sourceforge.net/p/rbtray/code/. 项目地址: https://gitcode.com/gh_mirrors/rb/rbtray 你是否厌倦了Windows任务栏上密密麻麻的图标&…

2026/7/6 0:01:17 阅读更多 →
Visual C++ 运行时库一键安装终极指南:告别DLL缺失烦恼

Visual C++ 运行时库一键安装终极指南:告别DLL缺失烦恼

Visual C 运行时库一键安装终极指南:告别DLL缺失烦恼 【免费下载链接】vcredist AIO Repack for latest Microsoft Visual C Redistributable Runtimes 项目地址: https://gitcode.com/gh_mirrors/vc/vcredist 你是否曾经遇到过这样的情况:下载了…

2026/7/6 0:05:19 阅读更多 →

周新闻

B站视频下载神器BiliTools:5分钟学会轻松保存任何B站内容

B站视频下载神器BiliTools:5分钟学会轻松保存任何B站内容

B站视频下载神器BiliTools:5分钟学会轻松保存任何B站内容 【免费下载链接】BiliTools A cross-platform bilibili toolbox. 跨平台哔哩哔哩工具箱,支持下载视频、番剧等等各类资源 项目地址: https://gitcode.com/GitHub_Trending/bilit/BiliTools …

2026/7/5 0:03:34 阅读更多 →
威胁模型全解析:从新手入门到实战应用,助你构建安全产品!

威胁模型全解析:从新手入门到实战应用,助你构建安全产品!

威胁模型的陌生现状在忙碌疲惫的一天里,参与了关于混合后量子密码学的讨论,应付端点攻击找茬的人,还参与留言板讨论后,发现“威胁模型”对多数人仍是陌生概念,且多被当作时髦用语。有趣的相关画作有一幅由 Embyr 创作的…

2026/7/5 0:03:34 阅读更多 →
渗透测试入门指南:从零基础到实战环境搭建

渗透测试入门指南:从零基础到实战环境搭建

1. 从“看热闹”到“入门”:我理解的渗透测试到底是什么?每次看到新闻里说某个大公司的数据被“黑”了,或者某个网站被攻击导致服务瘫痪,你是不是和我一样,心里会冒出两个念头:一是“这黑客真厉害”&#x…

2026/7/5 0:07:38 阅读更多 →

月新闻