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

On 19 May 2015 at 13:23, Robins Tharakan <tharakan(at)gmail(dot)com> wrote:
> Hi,
>
> Is there a way to know which rows were INSERTed and UPDATEd when doing a
> INSERT ... ON CONFLICT UPDATE? Probably via pseudo column indicating INSERT
> / UPDATE ?
>
> The RETURNING clause just allows us to return columns, but am unable to find
> a way to know 'what' happened to a given row.
>
> Any pointers would be helpful.
> Couldn't find anything related in 9.5devel docs either.

I don't think there's anything that tells you directly in the results
whether an INSERT or an UPDATE was performed. But you could use a
hack which is to return the xmax in the output, and if that's 0, it
INSERTed. If it's greater than 0, it UPDATEd:

e.g.

# INSERT INTO test (name, age) values ('Jack', 44) ON CONFLICT (name)
DO UPDATE SET age = EXCLUDED.age RETURNING xmax, *;
xmax | id | name | age
------+----+------+-----
0 | 70 | Jack | 44
(1 row)

# INSERT INTO test (name, age) values ('Jack', 44) ON CONFLICT (name)
DO UPDATE SET age = EXCLUDED.age RETURNING xmax, *;
xmax | id | name | age
---------+----+------+-----
1097247 | 70 | Jack | 44
(1 row)

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)

Regards

Thom