RETURNING 子句必须紧随DELETE之后且不可单独使用;仅支持DELETE FROM ... WHERE ... RETURNING ...;语法,返回删除前的行快照,常用于原子性删查、CTE归档等场景。

RETURNING 子句必须跟在 DELETE 之后,不能单独使用
PostgreSQL 的 DELETE 支持 RETURNING,但它不是独立语句,也不能写成 DELETE ... RETURNING ...; 以外的形式。常见错误是误以为它像 CTE 那样可前置,或试图在 PL/pgSQL 中漏掉分号导致语法报错。
正确结构只有一种:DELETE FROM table_name WHERE condition RETURNING column_list;。如果省略 WHERE,会删全表并返回所有被删行;漏掉 RETURNING 后的列名(比如只写 RETURNING *),虽合法但可能拖慢响应——尤其表有大字段或大量数据时。
-
RETURNING *返回整行,含所有列(包括自增、默认值、生成列等) - 只选必要字段,如
RETURNING id, created_at,减少网络传输和客户端内存占用 - 不能在
RETURNING中用别名覆盖原列名再参与后续逻辑(比如RETURNING id AS deleted_id是允许的,但无法在同一个 SQL 里用这个别名做计算)
在事务中组合 DELETE + RETURNING 可避免竞态问题
典型场景:删除一条待处理任务,并立刻拿到它的内容去执行。如果拆成 SELECT ... FOR UPDATE + DELETE 两步,中间可能被其他事务修改或删除同一行,导致不一致。
用 RETURNING 一步完成,由 PostgreSQL 在执行删除的同时锁定并返回数据,原子性强。注意:该行为依赖行级锁,若 WHERE 条件未命中索引,可能升级为全表扫描+锁表,拖慢并发。
- 确保
WHERE字段有索引(如status = 'pending' LIMIT 1要配合status索引) - 不要在
RETURNING里调用 volatile 函数(如NOW()、random()),它们会在每行返回时重新求值,结果不可预期 - 若需对返回结果做进一步处理,建议在应用层收数据后批量操作,而非拼进 CTE 或子查询里嵌套太深
RETURNING 和 WITH 子句配合能实现“删完就用”
当需要基于删除结果做插入、更新或其他关联操作时,WITH 是唯一干净的方式。比如把删掉的用户归档到历史表:
WITH deleted_users AS ( DELETE FROM users WHERE last_login < '2023-01-01' RETURNING id, email, created_at ) INSERT INTO users_archive SELECT * FROM deleted_users;
这里 deleted_users 是一个临时命名结果集,只能在当前语句中被后续 INSERT 引用。不能跨语句复用,也不能在函数里直接返回它——除非包装成 RETURN QUERY 的 PL/pgSQL 函数。
-
WITH中的DELETE仍受事务控制,失败则整个语句回滚 - 若
INSERT目标表有约束冲突(如唯一键重复),整个WITH语句失败,已删数据不会恢复——这点容易被忽略 - 不支持在
RETURNING中直接引用WITH上层定义的别名(即不能嵌套两层 WITH 并让内层 RETURNING 用外层别名)
应用代码里获取 RETURNING 结果要注意驱动兼容性
不是所有 PostgreSQL 客户端驱动都默认暴露 RETURNING 结果。例如 Python 的 psycopg2 需用 cursor.fetchall() 显式取回;而某些旧版 JDBC 驱动默认只返回影响行数,得加 ?stringtype=unspecified 参数或调用 getGeneratedKeys() 才能拿到。
- Node.js 的
pg模块中,client.query()的result.rows就是RETURNING数据,无需额外配置 - Go 的
pgx要用QueryRows()或QueryRow(),不能只用Exec() - 若 SQL 中写了
RETURNING但代码没读取结果,部分驱动会静默丢弃,不报错也不警告
最常被忽略的是:RETURNING 返回的是「被删时刻」的行快照,哪怕该行有触发器修改了其他字段,也不会体现在 RETURNING 结果里——它反映的是 DELETE 开始前的数据状态。

















