Re: Per row status during INSERT .. ON CONFLICT UPDATE?

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