返回表中在更新中实际更改的行 [英] Return rows of a table that actually changed in an UPDATE

问题描述

使用postgres，我可以执行UPDATE语句并返回受推荐影响的行。

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子句匹配的所有记录的列表，但不会告诉我操作实际更新了哪些行。

在这个简化的用例中，简单地添加另一个保护当然是微不足道的，然而，我的实际用例涉及从具有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。这些要求是有效的

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

自从这个问题被打开以来，我现在已经得到了大部分的工作，尽管我不确定我的方法是不是一个好主意-它有点被砍在一起了。

推荐答案

仅更新实际更改的行

这样可以在UPDATE之后节省昂贵的更新和昂贵的检查。

使用提供的新值更新每列(如果有更改)：

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

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

merge_accounts相同，只是所有非主键列都是数组类型

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

针对您的特殊情况

UPDATE accounts a
SET   (status, field1, field2)
    = (COALESCE(m.status[1], a.status)  -- default to original ..
     , COALESCE(m.field1[1], a.field1)   -- .. if m.column[1] IS NULL
     , COALESCE(m.field2[1], a.field2))
FROM   merge_accounts m
WHERE  m.uid = a.uid
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])
RETURNING a.*

如果merge_accounts中不应更新的列为空，则

m.status IS NOT NULL有效。
m.status <> '{}'如果操作空数组。
m.status[1] IS NOT NULL涵盖两个选项。

相关：

• Return pre-UPDATE column values using SQL only

这篇关于返回表中在更新中实际更改的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！