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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2015-05-19 17:53:09 +0530, Robins Tharakan wrote:

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 ?

No, not really.

The RETURNING clause just allows us to return columns, but am unable to
find a way to know 'what' happened to a given row.

There previously has been discussion about extending RETURNING to allow
to return the before/after row. But to me that's a mostly independent
feature to ON CONFLICT.

Greetings,

Andres Freund

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, May 19, 2015 at 10:49 AM, Andres Freund <andres@anarazel.de> wrote:

The RETURNING clause just allows us to return columns, but am unable to
find a way to know 'what' happened to a given row.

There previously has been discussion about extending RETURNING to allow
to return the before/after row. But to me that's a mostly independent
feature to ON CONFLICT.

That's certainly something we talked about. It could probably be done
with some kind of magical expression. I have to wonder how many of the
people that are sure that they need this really do, though. Is it
really natural to care about this distinction with idiomatic usage?

--
Peter Geoghegan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 19 May 2015 at 23:24, Peter Geoghegan <pg@heroku.com> wrote:

That's certainly something we talked about. It could probably be done
with some kind of magical expression. I have to wonder how many of the
people that are sure that they need this really do, though. Is it
really natural to care about this distinction with idiomatic usage?

Thanks everyone for responding promptly.

​Not sure if I can be authoritative for many, but for me, the need emanates
from having to move an ETL off MSSQL Server, which supports OUTPUT $action
(similar to RETURNING * in Postgres​) where $action is the per-row status
(INSERT / UPDATE).

My use-case is to create an extra row for all UPDATEd rows (only), which is
implemented in MSSQL by enveloping the MERGE with an INSERT (MERGE ...
OUTPUT $action) WHERE $action = 'UPDATE'.

Am still to test, but looks like Thom's reply earlier could take care of my
use-case, so we may need more people requesting this magic field, with a
valid use-case.

--
Robins Tharakan

On Tue, May 19, 2015 at 1:07 PM, Robins Tharakan <tharakan@gmail.com> wrote:

My use-case is to create an extra row for all UPDATEd rows (only), which is
implemented in MSSQL by enveloping the MERGE with an INSERT (MERGE ...
OUTPUT $action) WHERE $action = 'UPDATE'.

That could make sense. You can achieve something similar with per-row
triggers, perhaps.

Am still to test, but looks like Thom's reply earlier could take care of my
use-case, so we may need more people requesting this magic field, with a
valid use-case.

I'm not opposed to it, but it's not a personal priority to implement
this. I don't think it's great practice to use the hidden fields like
that. I can't see anything other than a dedicated expression serving
this purpose, if there is ever a documented solution.

--
Peter Geoghegan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, May 19, 2015 at 1:20 PM, Peter Geoghegan <pg@heroku.com> wrote:

On Tue, May 19, 2015 at 1:07 PM, Robins Tharakan <tharakan@gmail.com> wrote:

My use-case is to create an extra row for all UPDATEd rows (only), which is
implemented in MSSQL by enveloping the MERGE with an INSERT (MERGE ...
OUTPUT $action) WHERE $action = 'UPDATE'.

That could make sense. You can achieve something similar with per-row
triggers, perhaps.

BTW, be prepared to deal with the updated row (*any* row version) not
being visible to your MVCC snapshot with that pattern in Postgres (at
READ COMMITTED level). It probably won't matter, but it could.

--
Peter Geoghegan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers