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

Reply via email to