Postgres 18：RETURNING 子句中的 OLD 和 NEW 行

mahan

Postgres 18已于今日发布。在异步 I/O 和 UUIDv7 支持等主要功能下方，我们可以看到这个不错的小改进：

此版本增加了在 INSERT、UPDATE、DELETE 和 MERGE 命令的 RETURNING 子句中访问以前（OLD）和当前（NEW）值的功能。

它不像异步 I/O 那样引人注目，但它是那些在适当情况下非常宝贵的小功能之一。

UPDATE获取所有旧值和新值的简单演示：

UPDATE fruit
SET quantity = 300
WHERE item = 'Apples'
RETURNING OLD.*, NEW.*;

id |  item  | quantity | id |  item  | quantity
----+--------+----------+----+--------+----------
  5 | Apples |      200 |  5 | Apples |      300
(1 row)
OLD使用upsert检测新行
假设我们正在执行一个 upsert 操作，并希望区分返回的行RETURNING是新插入的行还是已更新的行。这在以前是可以实现的，但依赖于一个不直观的检查xmax = 0（参见下面最后一行）：

INSERT INTO webhook (
    id,
    data
) VALUES (
    @id,
    @data
)
ON CONFLICT (id)
    DO UPDATE SET id = webhook.id -- force upsert to return a row
RETURNING webhook.*,
    (xmax = 0) AS is_new;
该语句依赖于xmax将新插入的值设置为零，这是 Postgres 锁定实现的一个缺陷（请参阅有关此问题的完整解释）。它可以工作，但不是 API 的保证部分，并且可能随时发生变化。

在 Postgres 18 中，我们可以重新实现上述代码，使其更清晰易读，并且不依赖于实现细节。这也很简单——只需检查OLD返回子句中是否为空即可：

INSERT INTO webhook (
    id,
    data
) VALUES (
    @id,
    @data
)
ON CONFLICT (id)
    DO UPDATE SET id = webhook.id -- force upsert to return a row
RETURNING webhook.*,
    (OLD IS NULL)::boolean AS is_new;
复制
访问OLD和NEW无疑会有许多其他有用的案例，但这是一个让我们立即改进 18 岁之前代码的例子。