Menu

PostgreSQL RETURNING:INSERT、UPDATE、DELETE 返回数据

RETURNING 让 INSERT、UPDATE、DELETE 和 MERGE 返回它们修改的行:生成的 id、新的值、被删除的行,以及 MERGE 执行了哪种操作。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

RETURNING 让修改数据的语句像 SELECT 一样返回行。把它加在 INSERT、UPDATE、DELETE 或(从 PostgreSQL 17 开始)MERGE 的末尾,后面写上你想要的列或表达式:INSERT INTO users (email) VALUES ('ada@example.com') RETURNING id;。

两个新行都返回了,带着 PostgreSQL 生成的 id 和由默认值填好的 plan,不需要再查询一次。

拿到插入行的 id

RETURNING id 是获取生成键的标准方式。它报告的是行实际存储时的样子,所以对标识列、serial、UUID 默认值以及 BEFORE 触发器设置的值都有效。多行的 VALUES 或 INSERT ... SELECT 会为每个插入的行返回一行。

驱动读取它的方式和读取任何查询结果一样:Python 中 psycopg 用 cursor.fetchone(),Node.js 中 pg 用 result.rows。探索时用 RETURNING * 没问题;在应用代码中,要列出具体的列,这样表结构变化时结果也保持稳定。

查看 UPDATE 改了什么

在 UPDATE 中,RETURNING 显示修改之后的行,而且只包括满足 WHERE 的行:

返回两行:Book 变成 16.5,Lamp 变成 33。Pen 没有被修改,所以不在结果中。

在 PostgreSQL 17 中,RETURNING 只能看到新值。PostgreSQL 18 新增了 old 和 new(RETURNING old.price, new.price)。在 17 中,可以把表和它自己加锁后的副本连接起来:

每一行都带着修改前后的价格返回:10 和 15,20 和 25。

返回 DELETE 删除的行

DELETE ... RETURNING 返回它删除的行,可以用来记日志、做撤销,或者实现任务队列:工作进程删除一个任务,同时拿回它的数据。在 WITH 查询中,返回的行会交给下一条语句,这样一条语句就能在表之间移动行:

任务 1 和 3 离开 tasks,进入 done_tasks。这是一条语句,所以其他会话不会看到它们同时在两张表中,或者两张表中都没有。

查看 MERGE 执行了哪种操作

从 PostgreSQL 17 开始,MERGE 支持 RETURNING,函数 merge_action() 为每一行返回 INSERT、UPDATE 或 DELETE:

A-100 被更新为 7,C-300 以 4 被插入。在并发写入的情况下按键插入或更新,优先使用带 ON CONFLICT 的 upsert,它同样支持 RETURNING。

RETURNING 中可以写什么

SELECT 列表中能写的都可以:列、*、表达式、函数调用和别名(RETURNING price * qty AS total),但不能用聚合函数。要对返回的行计数或求和,把这条语句放进 WITH 查询,再在外层 SELECT 中聚合。

被 ON CONFLICT DO NOTHING 跳过的行,或者被返回 NULL 的 BEFORE 触发器跳过的行,不会被返回。

常见问题

PostgreSQL 中怎么拿到刚插入的行的 id?

在 INSERT 后面加上 RETURNING id。它在同一次往返中返回每个插入行生成的值,比 SELECT max(id) 更安全,对标识列、serial 以及 gen_random_uuid() 这样的默认值都适用。

RETURNING 比单独再执行一次 SELECT 快吗?

通常是的。它省掉了第二条语句和第二次往返服务器,而且返回的正是语句修改过的那些行,中间不会留下让其他会话修改它们的空档。

MySQL 支持 RETURNING 吗?

MySQL 没有 RETURNING 子句;插入之后要读取 LAST_INSERT_ID()。SQLite 从 3.35 版开始支持 RETURNING。

Coddy 编程语言插图

和 Coddy 一起学编程

开始