PostgreSQL 19 重磅新语法终于补齐这个缺口

IvorySQL

墨衍会员 · AI 创作全网分发码龄5年

关注

原创最新推荐文章于 2026-09-30 13:03:01 发布 325 阅读 AI 写同款· GEO 优化 ›

PostgreSQL的merge范例阅读详情

在 PostgreSQL 中，INSERT ... ON CONFLICT 是处理 Upsert（插入或更新）的核心语法之一。

自 PostgreSQL 9.5 引入该能力以来，开发者已经可以在插入冲突时选择忽略记录（DO NOTHING）或更新记录（DO UPDATE）。

在 PostgreSQL v19 中，这一语法进一步扩展：新增 ON CONFLICT ... DO SELECT。
当插入操作发生冲突时，可以直接返回已有记录，从而避免额外的查询操作。

这一特性为 “插入并返回记录” 的常见业务场景提供了更简洁的实现方式。

什么是 INSERT … ON CONFLICT？

INSERT … ON CONFLICT 是 PostgreSQL 实现 upsert 的语法，用于在插入数据时处理冲突行：当目标表中已存在冲突记录时，可以选择保持原记录不变或对其进行更新。

保持原记录不变可使用 ON CONFLICT DO NOTHING；更新冲突记录则使用 ON CONFLICT ... DO UPDATE SET ...。在执行更新时，需要指定冲突目标（conflict target），即用于检测冲突的约束（constraint）或唯一索引（unique index）。

之所以提供该语法，是因为其行为不同于 SQL 标准中的 MERGE。INSERT … ON CONFLICT 不存在竞态条件，在并发修改场景下仍能保证操作结果必然为 INSERT 或 UPDATE，不会因并发事务在两者之间删除冲突行而导致失败。

一个 MERGE 的并发问题示例

首先创建测试表：

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;

这说明 INSERT ... ON CONFLICT 在并发场景下更可靠。

PostgreSQL v19 新增 ON CONFLICT … DO SELECT

INSERT 或 UPDATE 的语义很容易理解，而 INSERT 或 SELECT 看起来可能有些反直觉。

实际上，DO SELECT 的含义是：

如果插入成功，则返回新插入的数据；
如果发生冲突，则直接返回已有记录。

该功能需要与 INSERT ... RETURNING 一起使用。

PostgreSQL v19 中的语法如下：

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

其中：

可以为返回的行添加行级锁。
返回列由 RETURNING 子句决定。

由于 SQL 语句返回的结果结构必须一致，因此 PostgreSQL 直接使用 RETURNING 列表作为 SELECT 的返回列。

DO SELECT 的典型使用场景

当 INSERT ... RETURNING 本身有价值时，DO SELECT 往往也非常有用。例如：

需要插入并返回记录 ID。
表中存在数据库自动生成字段。
字段会被触发器修改。
使用会进行截断或转换的数据类型。

在这些情况下，INSERT ... RETURNING 可以直接返回数据库最终存储的结果，而无需再执行额外查询。

经典示例：插入或返回已有记录

创建示例表：人员表，社保号唯一，ID 自增。

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;

但如果同时希望获取已存在记录的 ID，则可以使用 DO SELECT：

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

这样无论记录是新插入还是已经存在，都能够返回对应的 ID。

在旧版本 PostgreSQL 中的替代方案

在 PostgreSQL v19 之前，可以通过 DO UPDATE 实现类似效果：

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

这种写法虽然能够返回正确结果，但存在明显缺点：