DeepSeek总结的PostgreSQL v19 新特性：INSERT ... ON CONFLICT ... DO SELECT

l1t

569人浏览 · 2026-03-04 21:57:50

l1t · 2026-03-04 21:57:50 发布

原文地址：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

PostgreSQL 从 9.5 版本开始就支持 INSERT 语句的（非标准）ON CONFLICT 子句。在 v19 版本中，通过提交 88327092ff 增加了 ON CONFLICT ... DO SELECT。借此机会，我们重新审视一下 ON CONFLICT 的好处，并看看新的 DO SELECT 变体能带来哪些便利！

什么是 INSERT … ON CONFLICT？

INSERT ... 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 存在竞态条件的示例

创建如下表：

CREATE TABLE tab (key integer PRIMARY KEY, value integer);

然后开启一个事务并插入一行：

BEGIN;

INSERT INTO tab VALUES (1, 1);

在另一个并发会话中，运行一个 MERGE 语句：

MERGE INTO tab
USING (SELECT 1 AS key, 2 AS value) AS source
   ON source.key = tab.key
WHEN MATCHED THEN UPDATE SET value = source.value
WHEN NOT MATCHED THEN INSERT VALUES (source.key, source.value);

这个 MERGE 语句将会挂起。在您提交插入行的事务之后，MERGE 将引发一个错误：

ERROR:  duplicate key value violates unique constraint "tab_pkey"
DETAIL:  Key (key)=(1) already exists.

而相应的 INSERT ... ON CONFLICT ... DO UPDATE 语句则能正确地更新新行：

INSERT INTO tab VALUES (1, 2)
   ON CONFLICT (key)
   DO UPDATE SET value = EXCLUDED.value;

新特性 ON CONFLICT … DO SELECT 的描述

虽然"INSERT 或 UPDATE"对大多数人来说很容易理解，但"INSERT 或 SELECT"乍听起来可能令人困惑：提取数据怎么能替代插入数据呢？实际上，这个新子句只能与 INSERT ... RETURNING 结合使用。此外，v19 新子句的语法是：

DO SELECT [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } ] [ WHERE condition ]

因此，虽然可以选择对从表中选中的行加行锁，但不能指定列列表。如果再仔细想想，这其实是有道理的。毕竟，一个 SQL 语句的所有结果行必须具有相同的结构，所以 PostgreSQL 会直接使用 RETURNING 列表作为该语句的 SELECT 列表。

ON CONFLICT … DO SELECT 的使用场景

我认为，只要 INSERT ... RETURNING 有用的地方，这个新子句就有用武之地：比如表中包含由生成列或触发器填充的列，或者使用了会对插入数据进行四舍五入或截断的数据类型。一般来说，INSERT ... RETURNING 省去了您再次查询刚插入的行以确定数据库实际存储了什么值的麻烦。

举一个简单的例子，考虑一个人员表，它包含一个生成的主键、一个奥地利社会保障号（唯一）和一个姓名：

CREATE TABLE person (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   svnr varchar(10) UNIQUE NOT NULL,
   name text NOT NULL
);

如果您想知道序列为插入的行生成了哪个 id，可以使用：

INSERT INTO person (svnr, name) VALUES
   ('1750201068', 'Laurenz')
RETURNING id;

如果您尝试插入表中的数据可能已经存在于表中，您可以通过以下方式忽略重复行：

INSERT INTO person (svnr, name) VALUES
   ('1750201068', 'Laurenz'),
   ('1053080982', 'mary')
ON CONFLICT (svnr) DO NOTHING;

您也可以将 ON CONFLICT DO NOTHING 与 RETURNING 结合使用，但这样语句只会返回新插入行的 id。如果您需要那些您尝试插入但表中已存在的行的 id，您要么需要额外运行一个 SELECT 语句，要么可以使用新的 ON CONFLICT ... DO SELECT：

INSERT INTO person (svnr, name) VALUES
   ('1750201068', 'Laurenz'),
   ('1053080982', 'mary')
ON CONFLICT (svnr) DO SELECT
RETURNING id;

在旧版 PostgreSQL 中绕过缺乏 ON CONFLICT … DO SELECT 的方法

如果您不想为了检索"缺失"的行而执行第二条语句，有一种方法可以使用 ON CONFLICT ... DO UPDATE 达到相同的结果：

INSERT INTO person (svnr, name) VALUES
   ('1750201068', 'Laurenz'),
   ('1053080982', 'mary')
ON CONFLICT (svnr) DO UPDATE SET name = person.name
RETURNING id;