PostgreSQL 18 RETURNING 增强：一招解决数据同步与变更追踪难题

PostgreSQL 18的正式发布带来了不少重量级特性，其中对RETURNING子句的增强尤为引人注目。特别是与MERGE语句的结合，为现代应用的数据处理架构带来了实实在在的简化，尤其是在需要精准追踪数据变更的场景中，这项改进堪称一次重要突破。

RETURNING 子句的演进

RETURNING子句一直扮演着一个高效“信使”的角色。在INSERT、UPDATE、DELETE操作之后，它能立刻返回受影响行的数据，从而省去一次额外的SELECT查询，减少数据库往返次数，直接提升性能。然而，在PostgreSQL 18之前，这位“信使”的能力存在明显短板，迫使开发者们不得不采用各种复杂的替代方案。

在PostgreSQL 17中，社区首次为MERGE语句引入了RETURNING支持（提交c649fa24a），这标志着一次关键的进步。MERGE语句自PostgreSQL 15引入，其目标很明确：在单条原子语句中，根据条件完成INSERT、UPDATE或DELETE操作。但在缺少RETURNING支持的日子里，你很难直观地知道这条MERGE语句到底执行了哪种操作，具体影响了哪些数据。

PostgreSQL 18 的新特性

PostgreSQL 18 通过引入OLD与NEW别名（提交 80feb727c8，由 Dean Rasheed 提交，Jian He 与 Jeff Davis 评审），将RETURNING子句的能力提升到了一个全新的层级。这个看似简单的增强，从根本上改变了在DML操作期间捕获数据变更前后状态的方式。

PostgreSQL 18 之前的限制

回顾一下早期版本，RETURNING子句在不同语句类型下的限制可以用“各司其职，互不僭越”来形容：

• INSERT与UPDATE仅能返回新值或当前值。
• DELETE仅能返回旧值。
• MERGE则根据其内部实际执行的操作类型（INSERT、UPDATE或DELETE）返回对应的结果。

当你的应用需要对比某行数据更新前后的值，或者需要精确追踪某个字段是如何变化的时候，可选方案就变得非常有限且笨重：

1. 在修改数据前，额外执行一次SELECT查询。
2. 编写复杂的触发器函数来记录变更。
3. 把变更跟踪的逻辑完全放到应用层去实现。
4. 依赖诸如xmax之类的系统列进行间接推断。

这些方法普遍增加了实现的复杂度和访问延迟，更重要的是，它们把复杂性留给了开发者和运维人员，代码的可维护性也随之下降。这不禁让人思考，作为一款强大的关系型数据库，PostgreSQL能否在这一点上做得更好？

解决方案：OLD 与 NEW 别名

PostgreSQL 18 给出了肯定的答案。它引入了两个特殊的上下文别名：old与new。现在，你可以在同一条RETURNING子句中，同时访问到数据修改前的状态（old）和修改后的状态（new）。这个机制适用于INSERT、UPDATE、DELETE以及MERGE等所有DML操作。

基本语法示例如下：

UPDATE table_name
SET column = new_value
WHERE condition
RETURNING old.column AS old_value, new.column AS new_value;

为了避免与现有列名冲突，或者为了在类似触发器的上下文中使用，你还可以为这些别名进行重命名：

UPDATE accounts
SET balance = balance - 50
WHERE account_id = 123
RETURNING WITH (OLD AS previous, NEW AS current)
    previous.balance AS old_balance,
    current.balance AS new_balance;

MERGE + RETURNING：能力整合

在PostgreSQL 18中，MERGE与增强版RETURNING的组合，为经典的Upsert（插入或更新）场景提供了一套完整、强大的工具集。现在，你可以在一次原子操作中，同时完成数据的写入和变更结果的精准获取。

实践示例：产品库存系统

假设我们有一个产品库存管理系统，需要从外部数据源（例如一个临时表product_staging）同步数据。目标是：新增不存在的产品，更新已存在的产品，并且要准确记录每一行数据的处理结果。

步骤 1：创建数据表

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_code VARCHAR(50) UNIQUE NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INTEGER NOT NULL DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE product_staging (
    product_code VARCHAR(50),
    product_name VARCHAR(200),
    price DECIMAL(10, 2),
    stock_quantity INTEGER
);

步骤 2：插入初始数据

-- 主表初始数据
INSERT INTO products (product_code, product_name, price, stock_quantity)
VALUES
    ('LAPTOP-001', 'Premium Laptop', 999.99, 50),
    ('MOUSE-001', 'Wireless Mouse', 29.99, 200),
    ('KEYBOARD-001', 'Mechanical Keyboard', 79.99, 150);

-- 临时表（模拟外部数据源）数据
INSERT INTO product_staging (product_code, product_name, price, stock_quantity)
VALUES
    ('LAPTOP-001', 'Premium Laptop Pro', 1099.99, 45),  -- 更新现有产品
    ('MONITOR-001', '4K Monitor', 399.99, 75),           -- 新增产品
    ('MOUSE-001', 'Wireless Mouse', 29.99, 200);         -- 数据未实际改变

基础版：搭配 RETURNING 子句的 MERGE 操作

首先，我们看看如何使用基础的RETURNING来获取操作结果。

MERGE INTO products p
USING product_staging s ON p.product_code = s.product_code
WHEN MATCHED THEN
    UPDATE SET
        product_name = s.product_name,
        price = s.price,
        stock_quantity = s.stock_quantity,
        last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (product_code, product_name, price, stock_quantity)
    VALUES (s.product_code, s.product_name, s.price, s.stock_quantity)
RETURNING
    p.product_code,
    p.product_name,
    merge_action() AS action_performed;

返回结果示例：

 product_code  |    product_name     | action_performed
---------------+---------------------+------------------
 LAPTOP-001    | Premium Laptop Pro  | UPDATE
 MONITOR-001   | 4K Monitor          | INSERT
 MOUSE-001     | Wireless Mouse      | UPDATE

现在，我们一眼就能看出哪些行被更新了，哪一行是新插入的。

进阶版：搭配 OLD 与 NEW 别名的 MERGE 操作

这才是PostgreSQL 18的“王牌功能”。通过old和new别名，我们可以同时获取字段修改前和修改后的值，实现精细化的变更追踪与审计。

MERGE INTO products p
USING product_staging s ON p.product_code = s.product_code
WHEN MATCHED THEN
    UPDATE SET
        product_name = s.product_name,
        price = s.price,
        stock_quantity = s.stock_quantity,
        last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (product_code, product_name, price, stock_quantity)
    VALUES (s.product_code, s.product_name, s.price, s.stock_quantity)
RETURNING
    p.product_code,
    merge_action() AS action,
    old.product_name AS old_name,
    new.product_name AS new_name,
    old.price AS old_price,
    new.price AS new_price,
    old.stock_quantity AS old_stock,
    new.stock_quantity AS new_stock,
    (old.price IS DISTINCT FROM new.price) AS price_changed,
    (old.stock_quantity IS DISTINCT FROM new.stock_quantity) AS stock_changed;

注意，对于INSERT操作，old别名下的所有值都是NULL，而对于UPDATE操作，我们可以清晰地看到每个字段的变化情况。

 product_code  | action | old_name          | new_name            | old_price | new_price | old_stock | new_stock | price_changed | stock_changed
---------------+--------+-------------------+---------------------+-----------+-----------+-----------+-----------+---------------+--------------
 LAPTOP-001    | UPDATE | Premium Laptop    | Premium Laptop Pro  | 999.99    | 1099.99   | 50        | 45        | t             | t
 MONITOR-001   | INSERT | NULL              | 4K Monitor          | NULL      | 399.99    | NULL      | 75        | NULL          | NULL
 MOUSE-001     | UPDATE | Wireless Mouse    | Wireless Mouse      | 29.99     | 29.99     | 200       | 200       | f             | f

结果清晰地显示：笔记本电脑更新了价格和库存，显示器是新增的，而鼠标数据实际上没有任何变化。

构建审计日志

借助增强后的RETURNING子句，我们可以在不编写任何触发器的情况下，构建一个完整的、原子性的审计链路。这对于追求简洁高效的应用开发架构来说，意义重大。

步骤 1：创建审计表

CREATE TABLE product_audit (
    audit_id SERIAL PRIMARY KEY,
    product_code VARCHAR(50),
    action VARCHAR(10),
    old_values JSONB,
    new_values JSONB,
    changes JSONB,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

步骤 2：执行带详细审计追踪的 MERGE 操作

WITH merge_results AS (
    MERGE INTO products p
    USING product_staging s ON p.product_code = s.product_code
    WHEN MATCHED THEN
        UPDATE SET
            product_name = s.product_name,
            price = s.price,
            stock_quantity = s.stock_quantity,
            last_updated = CURRENT_TIMESTAMP
    WHEN NOT MATCHED THEN
        INSERT (product_code, product_name, price, stock_quantity)
        VALUES (s.product_code, s.product_name, s.price, s.stock_quantity)
    RETURNING
        p.product_code,
        merge_action() AS action,
        jsonb_build_object(
            'name', old.product_name,
            'price', old.price,
            'stock', old.stock_quantity
        ) AS old_values,
        jsonb_build_object(
            'name', new.product_name,
            'price', new.price,
            'stock', new.stock_quantity
        ) AS new_values
)
INSERT INTO product_audit (product_code, action, old_values, new_values, changes)
SELECT
    product_code,
    action,
    old_values,
    new_values,
    CASE
        WHEN action = 'INSERT' THEN new_values
        WHEN action = 'DELETE' THEN old_values
        ELSE (
            SELECT jsonb_object_agg(key, value)
            FROM jsonb_each(new_values)
            WHERE value IS DISTINCT FROM old_values->key
        )
    END AS changes
FROM merge_results;

步骤 3：查询审计追踪结果

select * from product_audit;

（输出结果与原文一致，清晰地记录了每次变更的旧值、新值及具体变化内容。）

这个示例巧妙地利用了公共表表达式（CTE）先捕获MERGE操作的结果，然后将新旧值以及计算出的差异（changes）以JSONB格式写入审计表。整个过程是原子的，数据同步和审计记录生成在一次操作内完成，极大地提升了可靠性和工程上的简洁性。

总结

PostgreSQL 18 对RETURNING子句的增强，特别是OLD与NEW别名的引入，为INSERT、UPDATE、DELETE以及功能强大的MERGE操作提供了前所未有的数据变更可视性。这一能力显著降低了对触发器和额外查询的依赖，使得数据同步、变更追踪与审计等通用模式的实现变得更加简洁、高效且易于维护。

MERGE与增强型RETURNING的强强联合，为现代应用的Upsert场景提供了极佳的控制能力与透明度。这不仅是PostgreSQL在功能上的一个重要里程碑，更是其在开发友好性和实用性方面持续投入的明证。对于正在使用或考虑使用PostgreSQL的开发者而言，深入理解并应用这一特性，无疑能帮助你构建出更健壮、更优雅的数据处理层。

---

原文链接：https://www.pgedge.com/blog/postgresql-18-returning-enhancements-a-game-changer-for-modern-applications
欢迎在云栈社区分享你的PostgreSQL使用心得与实践经验。