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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers