How to get value before update

Question

Table is updated with value from other table. How to get values before and after update ? Tried

CREATE temp table change ( id integer, testvalue varchar );
insert into change values (1, 'old' );

CREATE temp table newvalue ( id integer, testvalue varchar );
insert into newvalue  values (1, 'new' );

update change set testvalue =newvalue.testvalue
from newvalue
 where change.id=newvalue.id
returning change.testvalue, newvalue.testvalue

but in returns

new, new

How to get

old, new

result?

Answer 1

The manual explains:

The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in FROM, can be computed. The new (post-update) values of the table's columns are used. The syntax of the RETURNING list is identical to that of the output list of SELECT.

so to get the "old2 value the table must be in the FROM clause like

CREATE temp table change ( id integer, testvalue varchar );
insert into change values (1, 'old' );

CREATE temp table newvalue ( id integer, testvalue varchar );
insert into newvalue  values (1, 'new' );

update change set testvalue =newvalue.testvalue
from change c1 JOIN newvalue ON c1.id=newvalue.id
 where change.id=newvalue.id 
returning c1.testvalue, newvalue.testvalue

CREATE TABLE

INSERT 0 1

CREATE TABLE

INSERT 0 1

testvalue	testvalue
old	new

UPDATE 1

fiddle