Return table rows that were actually changed on UPDATE

Question

Using Postgres, I can execute the update statement and return the lines affected by 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 the records matched against the sentence WHERE, but it will not tell me which rows were actually updated by the operation.

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

The best that I have left 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

, Java- , .

, postgres , - ?

github - , , . Postgres 9.1, 9.4.

• /
• , , upsert
• - .

, , , - - .

Answer 1

, .

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

:

• ( ) SELECT DISTINCT ?
• 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.*;

NULL. .
, (quote):

merge_accounts , , non-pk

, , .. , , , .

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 , , , NULL merge_accounts.
m.status <> '{}', .
m.status[1] IS NOT NULL .

:

Jayadevan , :

• UPDATE Column SQL Only - PostgreSQL

Answer 2

, ,

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

Answer 3

information_schema.columns , plpgsql UPDATE.

. DDL:

create table foo
(
  id serial,
  val integer,
  name text
);

insert into foo (val, name) VALUES (10, 'foo'), (20, 'bar'), (30, 'baz');

:

select column_name
from information_schema.columns
where table_name = 'foo'
order by ordinal_position;

, DDL.

Essentially, you should use the above SELECTin the function to dynamically create your statement UPDATEby repeating the results of the above SELECTin FOR LOOPto dynamically create both SET, and WHERE.

Answer 4

Some variation on this?

SELECT * FROM old;
 id | val 
----+-----
  1 |   1
  2 |   2
  4 |   5
  5 |   1
  6 |   2

SELECT * FROM new;
 id | val 
----+-----
  1 |   2
  2 |   2
  3 |   2
  5 |   1
  6 |   1

SELECT * FROM old JOIN new ON old.id = new.id;
 id | val | id | val 
----+-----+----+-----
  1 |   1 |  1 |   2
  2 |   2 |  2 |   2
  5 |   1 |  5 |   1
  6 |   2 |  6 |   1
(4 rows)

 WITH sel AS (
               SELECT o.id , o.val  FROM old o JOIN new n ON o.id=n.id  ),
 upd AS (
              UPDATE old  SET val = new.val  FROM new WHERE new.id=old.id RETURNING old.*  )
 SELECT * from sel, upd WHERE sel.id = upd.id AND sel.val <> upd.val;
 id | val | id | val 
----+-----+----+-----
  1 |   1 |  1 |   2
  6 |   2 |  6 |   1
(2 rows)

See the SO answer and read the entire discussion.