在 PostgreSQL 中INSERT ... ON CONFLICT是处理 Upsert插入或更新 的核心语法之一。自 PostgreSQL 9.5 引入该能力以来开发者已经可以在插入冲突时选择 忽略记录DO NOTHING 或 更新记录DO UPDATE。在 PostgreSQL v19 中这一语法进一步扩展新增ON CONFLICT ... DO SELECT。当插入操作发生冲突时可以直接返回已有记录从而避免额外的查询操作。这一特性为 “插入并返回记录” 的常见业务场景提供了更简洁的实现方式。什么是 INSERT … ON CONFLICTINSERT … 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);在另一个并发会话执行 MERGEMERGE 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 SELECTINSERT 或 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 );插入数据并返回自动生成的 IDINSERT 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 SELECTINSERT 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;这种写法虽然能够返回正确结果但存在明显缺点每次 UPDATE 都会产生 死元组dead tuple需要 VACUUM 清理。如果不是 HOT 更新所有索引都需要被修改。即使数据未变化也会产生额外写入开销。因此在性能和存储效率上并不理想。为什么没有 ON CONFLICT … DO DELETE这种语法几乎没有实际应用场景因此没有被引入。相比之下用户更希望增加的能力是在 ON CONFLICT 子句中支持多个冲突约束arbiter constraint而不仅仅是一个。总结PostgreSQL v19 新增的ON CONFLICT ... DO SELECT为INSERT ... ON CONFLICT语法补齐了一个重要能力。该特性在以下场景中尤为实用需要插入或获取已有记录表字段由数据库自动生成数据会被触发器修改需要在插入时直接获取最终存储结果通过这一增强PostgreSQL 在 Upsert 语义和返回结果处理方面变得更加完整也减少了业务层额外查询的复杂度。原文链接https://www.cybertec-postgresql.com/en/insert-on-conflict-do-select-a-new-feature-in-postgresql-v19/作者Laurenz Albe
PostgreSQL 19 重磅新语法终于补齐这个缺口
发布时间：2026/7/7 7:11:32
在 PostgreSQL 中INSERT ... ON CONFLICT是处理 Upsert插入或更新 的核心语法之一。自 PostgreSQL 9.5 引入该能力以来开发者已经可以在插入冲突时选择 忽略记录DO NOTHING 或 更新记录DO UPDATE。在 PostgreSQL v19 中这一语法进一步扩展新增ON CONFLICT ... DO SELECT。当插入操作发生冲突时可以直接返回已有记录从而避免额外的查询操作。这一特性为 “插入并返回记录” 的常见业务场景提供了更简洁的实现方式。什么是 INSERT … ON CONFLICTINSERT … 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);在另一个并发会话执行 MERGEMERGE 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 SELECTINSERT 或 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 );插入数据并返回自动生成的 IDINSERT 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 SELECTINSERT 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;这种写法虽然能够返回正确结果但存在明显缺点每次 UPDATE 都会产生 死元组dead tuple需要 VACUUM 清理。如果不是 HOT 更新所有索引都需要被修改。即使数据未变化也会产生额外写入开销。因此在性能和存储效率上并不理想。为什么没有 ON CONFLICT … DO DELETE这种语法几乎没有实际应用场景因此没有被引入。相比之下用户更希望增加的能力是在 ON CONFLICT 子句中支持多个冲突约束arbiter constraint而不仅仅是一个。总结PostgreSQL v19 新增的ON CONFLICT ... DO SELECT为INSERT ... ON CONFLICT语法补齐了一个重要能力。该特性在以下场景中尤为实用需要插入或获取已有记录表字段由数据库自动生成数据会被触发器修改需要在插入时直接获取最终存储结果通过这一增强PostgreSQL 在 Upsert 语义和返回结果处理方面变得更加完整也减少了业务层额外查询的复杂度。原文链接https://www.cybertec-postgresql.com/en/insert-on-conflict-do-select-a-new-feature-in-postgresql-v19/作者Laurenz Albe
 获取与实战)
