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
