Laurenz Albe：INSERT ... ON CONFLICT ... DO SELECT：PostgreSQL v19 的新特性
内容提要
PostgreSQL 9.5 起支持 INSERT 的 ON CONFLICT 子句实现 upsert。v19 新增 ON CONFLICT ... DO SELECT，需配合 RETURNING 使用。相比 MERGE，ON CONFLICT 无竞态条件，能保证插入或更新成功。DO SELECT 适合获取已存在行的数据，避免额外查询，尤其适用于生成列或触发器修改数据的场景。
延伸解读
ON CONFLICT 与 MERGE 的竞态条件差异
文章指出，ON CONFLICT 与 MERGE 的关键区别在于竞态条件。MERGE 在并发场景下可能因冲突行被其他事务删除而失败，而 INSERT ... ON CONFLICT ... DO UPDATE 能保证要么插入要么更新，不会因并发修改而报错。这解释了 PostgreSQL 为何在已有 MERGE 的情况下仍保留非标准的 ON CONFLICT 语法。
DO SELECT 的语法限制与设计逻辑
新特性 ON CONFLICT ... DO SELECT 必须与 INSERT ... RETURNING 一起使用，且不能指定列列表，只能通过 RETURNING 定义返回结构。这是因为 SQL 语句的所有结果行必须结构一致，PostgreSQL 直接复用 RETURNING 列表作为 SELECT 列表。虽然可以对选中行加锁，但无法自定义查询列。
DO SELECT 的适用场景
DO SELECT 适用于需要获取已存在行数据的场景，尤其是表中有生成列、触发器修改数据，或数据类型会舍入/截断插入值时。传统上，若想同时获得新插入行和已存在行的 id，需要额外执行 SELECT，而 DO SELECT 能在一条语句中返回所有尝试插入行的 id，避免二次查询。
替代方案 DO UPDATE 的代价
文章提到，若不使用 DO SELECT，也可用 ON CONFLICT ... DO UPDATE 实现类似效果，但即使更新不改变任何数据，也会产生实际更新操作，带来明显缺点，例如增加 VACUUM 负担。因此，DO SELECT 提供了一种更轻量的选择，尤其适合只读获取已存在行信息的场景。
Q&A
PostgreSQL v19 新增的 ON CONFLICT ... DO SELECT 是什么？
ON CONFLICT ... DO SELECT 是 PostgreSQL v19 引入的新特性，允许在 INSERT 语句中，当发生冲突时，不执行插入或更新，而是返回已存在行的数据。它必须与 RETURNING 子句一起使用，语法为 INSERT ... ON CONFLICT ... DO SELECT ... RETURNING ...。
ON CONFLICT 和 MERGE 在并发场景下有什么主要区别？
ON CONFLICT 没有竞态条件，而 MERGE 有。具体来说，INSERT ... ON CONFLICT ... DO UPDATE 保证要么插入要么更新成功，即使有并发修改也不会失败；而 MERGE 在并发事务删除冲突行时可能出错。
什么时候应该使用 ON CONFLICT ... DO SELECT？
当表中有生成列或触发器修改数据，且你需要获取已存在行的数据（例如生成的主键 id）时，使用 DO SELECT 可以避免额外的 SELECT 查询。它特别适用于 INSERT ... RETURNING 有用的场景，比如需要知道数据库实际存储的值。
ON CONFLICT ... DO SELECT 的语法有什么限制？
DO SELECT 只能与 INSERT ... RETURNING 一起使用，不能单独使用。它不能指定列列表，因为结果行的结构必须与 RETURNING 列表一致。此外，可以选择对选中的行加锁，但语法上不支持指定列。
如何在不使用 DO SELECT 的情况下，用 ON CONFLICT 获取已存在行的数据？
可以使用 ON CONFLICT ... DO UPDATE 执行一个不改变任何数据的更新（例如 SET 某列为原值），这样也能返回已存在行的数据。但这种方法有缺点：会产生不必要的更新，导致表膨胀和 VACUUM 负担。
PostgreSQL 的 ON CONFLICT 子句是从哪个版本开始支持的？
ON CONFLICT 子句自 PostgreSQL 9.5 版本开始支持，用于实现 upsert 功能。
