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