Thread: 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

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

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

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

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

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