如何将PostgreSQL 9.4的WITH UPDATE语句改写适配PostgreSQL 9.0

首先明确一点：PostgreSQL 9.0完全不支持WITH子句中包含UPDATE（或DELETE）操作——这个版本的WITH仅能用于定义SELECT类型的子查询，没办法像9.4及以后版本那样在WITH里直接写数据修改语句并返回结果。

如果要把PostgreSQL 9.4的WITH UPDATE语句改写成9.0兼容的版本，得根据你的具体场景选择下面的方案：

方案1：拆分语句（简单场景适用） #

如果你的业务场景没有高并发压力，或者可以接受两次查询之间的微小数据差异，可以直接把原WITH UPDATE拆成独立的UPDATE和SELECT语句。

举个例子，假设你9.4的原语句是这样的：

WITH updated_rows AS (
    UPDATE orders
    SET status = 'completed'
    WHERE order_date < NOW() - INTERVAL '30 days'
    RETURNING *
)
SELECT count(*) FROM updated_rows;

改成9.0兼容的写法就是：

-- 第一步：执行更新操作
UPDATE orders
SET status = 'completed'
WHERE order_date < NOW() - INTERVAL '30 days';

-- 第二步：统计实际更新的行数
SELECT COUNT(*) FROM orders
WHERE order_date < NOW() - INTERVAL '30 days' AND status = 'completed';

⚠️ 注意：这个方法在并发环境下可能存在统计误差，因为两次查询之间可能有其他会话修改了目标表的数据。

方案2：借助临时表（并发场景更可靠） #

如果需要确保统计的行数和实际更新的行数完全一致，建议用临时表先锁定要更新的记录主键，再执行更新和统计：

-- 1. 创建会话私有临时表，存储要更新的记录主键
CREATE TEMP TABLE temp_order_updates AS
SELECT order_id FROM orders
WHERE order_date < NOW() - INTERVAL '30 days';

-- 2. 根据临时表的主键执行精准更新
UPDATE orders
SET status = 'completed'
WHERE order_id IN (SELECT order_id FROM temp_order_updates);

-- 3. 直接从临时表统计行数，结果100%准确
SELECT COUNT(*) FROM temp_order_updates;

-- 临时表会在会话结束后自动删除，也可以手动清理
DROP TABLE temp_order_updates;

这个方法能彻底避免并发场景下的数据不一致问题，是更稳妥的选择。

总的来说，PostgreSQL 9.0没有和WITH UPDATE直接等效的功能，必须通过拆分语句或临时表的方式来实现相同的业务逻辑。

内容的提问来源于stack exchange，提问作者oginski