PostgreSQL中CTE内的DELETE只能操作主表，不能直接删除历史表；正确做法是用DELETE RETURNING导出数据，再通过主查询INSERT INTO历史表实现原子归档。
CTE里DELETE只能删主表，不能直接删历史表
PostgreSQL的WITH子句中，DELETE语句的目标表必须是最终执行的主DELETE操作所指向的表，不能在CTE内部对其他表（比如历史表）发起DELETE或INSERT。想“一边删原表、一边插历史表”，得靠RETURNING把数据传出来，再用CTE链式承接。
用WITH ... DELETE ... RETURNING + INSERT INTO实现原子移动
核心思路是：先用DELETE从原表移出数据并RETURNING *，再把这个结果集作为CTE的输出，在外部INSERT INTO history_table。整个操作可封装在一个事务中保证原子性。
常见错误是试图在CTE里写两个独立DML：WITH del AS (DELETE ... RETURNING *), ins AS (INSERT INTO hist SELECT * FROM del) —— 这会报错ERROR: syntax error at or near "INSERT"，因为CTE里只允许SELECT/INSERT/UPDATE/DELETE/VALUES，但**不能把INSERT作为CTE成员**（除非是INSERT ... RETURNING且被上层引用）。
- 正确写法是把
DELETE ... RETURNING作为CTE，然后在主查询中INSERT INTO history_table SELECT * FROM cte_name - 注意字段顺序和类型必须严格匹配，建议显式列出列名，避免因表结构变更导致插入失败
- 如果历史表有额外字段（如
deleted_at、op_type），需在SELECT中补全：SELECT *, NOW(), 'move' FROM del
带WHERE条件和事务控制的实际写法
假设要将orders表中status = 'completed'且updated_at < '2024-01-01'的记录归档到orders_history：
PostgreSQL 18.4 官方 Ubuntu 安装包现已发布，这是目前最新的稳定版本。推荐通过官方 APT 仓库安装：先执行 sudo apt update 更新索引，再运行 sudo apt install postgresql-18 即可完成部署。新版本引入了异步 I/O 子系统，在顺序扫描与 VACUUM 场景下性能提升显著，同时支持 UUID v7 原生生成函数与虚拟生成列。
BEGIN; WITH moved AS ( DELETE FROM orders WHERE status = 'completed' AND updated_at < '2024-01-01' RETURNING id, customer_id, amount, status, created_at, updated_at ) INSERT INTO orders_history (id, customer_id, amount, status, created_at, updated_at, archived_at) SELECT id, customer_id, amount, status, created_at, updated_at, NOW() FROM moved; COMMIT;
关键点：
-
RETURNING必须包含所有要插入历史表的源字段；若历史表多出archived_at，就在SELECT里用NOW()补上 - 不加事务的话，万一
INSERT失败，数据已在DELETE中丢失——这点极易被忽略 - 大表操作前建议在
WHERE条件上确认有索引，否则DELETE可能锁表太久
替代方案：使用触发器或逻辑复制？
如果这是高频操作（比如每小时归档），硬编码CTE+事务会增加应用层负担，也难统一异常处理。这时更稳的做法是：
- 在原表上建
BEFORE DELETE触发器，自动把OLD.*写入历史表（注意触发器里不能提交事务） - 或用
pg_cron定时跑上面那段CTE脚本，配合log_min_duration_statement监控慢操作 - 不要用
LISTEN/NOTIFY异步搬运——DELETE后通知再插入，无法保证数据不丢失
真正麻烦的从来不是语法，而是归档窗口判断是否准确、历史表分区是否就绪、以及磁盘空间告警有没有配好。
