On 19 May 2015 at 13:23, Robins Tharakan <[email protected]> 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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
