Thread: insert on conflict postgres returning distinction

is there any way to distinct between updated and inserted rows in RETURNING
clause when ON CONFLICT UPDATE was used?



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 8/12/20 7:23 AM, pinker wrote:
> is there any way to distinct between updated and inserted rows in RETURNING
> clause when ON CONFLICT UPDATE was used?

Do you want to use that information immediately in the query or store it 
somewhere? If the first case I don't think that is possible. For the 
second then, I haven't tried it but,  maybe a trigger using transition 
tables per example here:

https://www.postgresql.org/docs/12/plpgsql-trigger.html

Example 42.7. Auditing with Transition Tables

> 
> 
> 
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

thank you Adrian,
the background of it is that I have already written the python script that
translates Oracle MERGE clause to Postgres INSERT ... ON CONFLICT, but in
order to be able to add DELETE part from MERGE i need to distinct those
operations. 

thank you for the idea with trigger, i haven't thought about it at the
beginning, but it does complicate the code a lot :/ not saying about
performance... 



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

how about this solution?
Does it have any caveats?

WITH upsert AS (INSERT INTO GUCIO (ID, NAZWA)
    SELECT A.ID, A.NAZWA
    FROM ALA A
    ON CONFLICT (ID) DO UPDATE SET
        nazwa = excluded.nazwa
    RETURNING xmax,xmin, *)
select xmax as xmax_value
into txmaxu
from upsert;

delete
from gucio
where xmax = (select xmax_value from txmaxu where xmax_value <> 0 limit 1);



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Od course inside transaction block



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html