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?
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
