On 5/19/15 3:04 PM, Thom Brown wrote:
If you want the delta, you'll have to resort to a CTE:e.g. # WITH newvals AS ( INSERT INTO test (name, age) VALUES ('James', 45) ON CONFLICT (name) DO UPDATE SET age = EXCLUDED.age RETURNING *) SELECT n.name, o.age as "old.age", n.age as "new.age" FROM test o RIGHT JOIN newvals n on o.name = n.name; name | old.age | new.age -------+---------+--------- James | 44 | 45 (1 row)
Also note that the old value is not the actual value right before the update, but one according to a snapshot taken at the beginning of the query. So if you instead did SET age = age + 1, you could see an old value of 44 and a new value of 46 (or any similarly weird combination of values).
.m -- Sent via pgsql-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
