主页 / dba / 问题 / 308436
Accepted
cis
Asked: 2022-03-09 06:02:25 +0800 CST

在查询中返回列是否已被当前查询更改

  • 772

我有一个像这样的查询

UPDATE my_table 
  SET my_value = 
    CASE WHEN random() > 0.5 THEN my_value * 2 ELSE my_value END
RETURNING *;

现在，在RETURNING语句中，我想要一个布尔值，指示my_value当前查询是否已更改。

假设，我不能将之前的值my_value作为参数传递给查询。

那么，有没有办法获得类似在 之后具有不同值的列的列表UPDATE？还是在 in 之前的状态下获取UPDATE值RETURNING？

在我的示例中，我当然可以将结果放入random()CTE 中，例如

WITH random_cte AS (
    SELECT random() AS my_random
)
UPDATE my_table 
  SET my_value = 
    CASE WHEN my_random > 0.5 THEN my_value * 2 ELSE my_value END
FROM random_cte
RETURNING *, my_random > 0.5 AS value_changed;

但这会使查询有些膨胀。所以我想知道我是否可以以更优雅的方式做到这一点？

postgresql update

1 个回答

  1. Best Answer

    这两个查询是不等价的。第一个查询将评估每一行的VOLATILE函数random()，而第二个查询在 CTE 中评估一次，因此my_random对所有行都是相同的。

    不，CTE 不会被内联。手册：

    但是，如果WITH查询是非递归且无副作用的（即，它不SELECT 包含 volatile 函数），则可以将其折叠到父查询中

    大胆强调我的。

    但这可能只是构建测试时的一个意外。

    任一查询都会更新所有行，即使没有任何变化。

    为了获得value_changed可靠的结果，请比较 pre-UPDATE和 post-UPDATE值：

    UPDATE my_table t
SET    my_value = CASE WHEN random() > 0.5 THEN t.my_value * 2 ELSE t.my_value END
FROM  (SELECT id, my_value FROM my_table) pre
WHERE  t.id = pre.id
RETURNING t.*, t.my_value IS DISTINCT FROM pre.my_value AS value_changed;

    id是 PK 或任何其他（组合）唯一的非空列。

    random() > 0.5可以true并且my_value仍然保持不变。想想NULL或0。

    并发写入负载下存在潜在的竞争条件。看：

    如果您的用例真的那么简单（只有一个列要更新），您宁愿从一开始就禁止空更新。看：

    • 2

相关问题