sql - 返回在 UPDATE 中实际更改的表的行

Question

使用 Postgres，我可以执行更新语句并返回受命令影响的行。

UPDATE accounts
SET status = merge_accounts.status,
    field1 = merge_accounts.field1,
    field2 = merge_accounts.field2,
    etc.
FROM merge_accounts WHERE merge_accounts.uid =accounts.uid
RETURNING accounts.*

这将为我提供与 WHERE 子句匹配的所有记录的列表，但不会告诉我操作实际更新了哪些行。

在这个简化的用例中，简单地添加另一个守卫 AND status != 'Closed 当然是微不足道的，但是我的真实世界用例涉及从合并中更新可能的几十个字段有 10,000 多行的表，我希望能够检测到哪些行实际发生了更改，哪些与之前的版本相同。 (预计很少有行会实际更改)。

到目前为止我得到的最好的是

UPDATE accounts
SET x=..., y=...
FROM accounts as old WHERE old.uid = accounts.uid
FROM merge_accounts WHERE merge_accounts.uid = accounts.uid
RETURNING accounts, old

这将返回一个包含旧行和新行的元组，然后可以在我的 Java 代码库本身内部对其进行区分 - 但这需要大量额外的网络流量，并且可能容易出错。

理想的情况是能够让 postgres 只返回实际更改了任何值的行 - 这可能吗？

Here on github是我正在做的事情的一个更真实的例子，结合了目前为止的一些建议。
使用 Postgres 9.1，但如果需要可以使用 9.4。要求有效

• 能够执行新数据的更新
• 我们可能只知道要在任何给定行上更新的特定键/值对
• 返回一个结果，其中仅包含更新插入实际更改的行
• 奖励 - 还可以获得旧记录的副本。

自从这个问题被打开以来，我现在已经完成了大部分工作，尽管我不确定我的方法是否是个好主意——它有点乱七八糟。

Answer 1

只更新实际发生变化的行

这节省了昂贵的更新和 UPDATE 之后的昂贵检查.

使用提供的新值更新每一列(如果有任何变化):

UPDATE accounts a
SET   <b>(status,   field1,   field2)</b>  -- short syntax for  ..
  <b>= (m.status, m.field1, m.field2)</b>  -- .. updating multiple columns
FROM   merge_accounts m
WHERE  m.uid = a.uid
AND   <b>(a.status IS DISTINCT FROM m.status OR
       a.field1 IS DISTINCT FROM m.field1 OR 
       a.field2 IS DISTINCT FROM m.field2)</b>
RETURNING a.*;

由于 PostgreSQL 的 MVCC 模型任何对行的更改都会写入新的行版本。更新单个列几乎与一次更新行中的每一列一样昂贵。只要您需要更新任何内容，重写该行的其余部分几乎是免费的。

详细信息:

• How do I (or can I) SELECT DISTINCT on multiple columns?
• UPDATE a whole row in PL/pgSQL

整行的简写

如果 accounts 的行类型和 merge_accounts是相同的并且您想从merge_accounts 中采用一切进入accounts ，有一个比较整行类型的快捷方式:

UPDATE accounts a
SET   (status,   field1,   field2)
  = (m.status, m.field1, m.field2)
FROM   merge_accounts m
WHERE  a.uid = m.uid
<b>AND    m IS DISTINCT FROM a</b>
RETURNING a.*;

这甚至适用于 NULL 值。 Details in the manual.
但它不会适用于您自己开发的解决方案(引用您的评论):

merge_accounts is identical, save that all non-pk columns are array types

它需要兼容的行类型，即每列共享相同的数据类型，或者至少在两种类型之间存在隐式转换。

针对您的特殊情况

UPDATE accounts a
SET   (status, field1, field2)
    = <b>(COALESCE(m.status[1], a.status)</b>  -- default to original ..
     <b>, COALESCE(m.field1[1], a.field1)</b>   -- .. if m.column[1] IS NULL
     <b>, COALESCE(m.field2[1], a.field2))</b>
FROM   merge_accounts m
WHERE  m.uid = a.uid
<b>AND   (m.status[1] IS NOT NULL AND a.status IS DISTINCT FROM m.status[1]
    OR m.field1[1] IS NOT NULL AND a.field1 IS DISTINCT FROM m.field1[1]
    OR m.field2[1] IS NOT NULL AND a.field2 IS DISTINCT FROM m.field2[1])</b>
RETURNING a.*

m.status IS NOT NULL如果不应更新的列在 merge_accounts 中为 NULL，则有效.
m.status <> '{}'如果您使用空数组进行操作。
m.status[1] IS NOT NULL涵盖两个选项。

相关:

• Return pre-UPDATE column values using SQL only