Return rows of a table that actually changed in an UPDATE

Question

Using Postgres, I can perform an update statement and return the rows affected by the commend.

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.*

This will give me a list of all records that matched the WHERE clause, however will not tell me which rows were actually updated by the operation.

In this simplified use-case it of course would be trivial to simply add another guard AND status != 'Closed, however my real world use-case involves updating potentially dozens of fields from a merge table with 10,000+ rows, and I want to be able to detect which rows were actually changed, and which are identical to their previous version. (The expectation is very few rows will actually have changed).

The best I've got so far is

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

Which will return a tuple of old and new rows that can then be diff'ed inside my Java codebase itself - however this requires significant additional network traffic and is potentially error prone.

The ideal scenario is to be able to have postgres return just the rows that actually had any values changed - is this possible?

Here on github is a more real world example of what I'm doing, incorporating some of the suggestions so far.
Using Postgres 9.1, but can use 9.4 if required. The requirements are effectively

• Be able to perform an upsert of new data
• Where we may only know the specific key/value pair to update on any given row
• Get back a result containing just the rows that were actually changed by the upsert
• Bonus - get a copy of the old records as well.

Since this question was opened I've gotten most of this working now, although I'm unsure if my approach is a good idea or not - it's a bit hacked together.

Answer 1

Only update rows that actually change

That saves expensive updates and expensive checks after the UPDATE.

To update every column with the new value provided (if anything changes):

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.*;

Due to PostgreSQL's MVCC model any change to a row writes a new row version. Updating a single column is almost as expensive as updating every column in the row at once. Rewriting the rest of the row comes at practically no cost, as soon as you have to update anything.

Details:

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

Shorthand for whole rows

If the row types of accounts and merge_accounts are identical and you want to adopt everything from merge_accounts into accounts, there is a shortcut comparing the whole row type:

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.*;

This even works for NULL values. Details in the manual.
But it's not going to work for your home-grown solution where (quoting your comment):

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

It requires compatible row types, i.e. each column shares the same data type or there is at least an implicit cast between the two types.

For your special case

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.*

m.status IS NOT NULL works if columns that shouldn't be updated are NULL in merge_accounts.
m.status <> '{}' if you operate with empty arrays.
m.status[1] IS NOT NULL covers both options.

Related:

• Return pre-UPDATE column values using SQL only

Answer 2

if you aren't relying on side-effectts of the update, only update the records that need to change

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
 AND NOT (status IS NOT DISTINCT FROM merge_accounts.status 
      AND field1 IS NOT DISTINCT FROM merge_accounts.field1 
      AND field2 IS NOT DISTINCT FROM merge_accounts.field2
      )
RETURNING accounts.*