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

On 19 May 2015 at 13:23, Robins Tharakan <tharakan@gmail.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