sumek sumek - 8 days ago 5
SQL Question

Postres upsert: distinguish between new and updated rows

I'm thinking of using PostgreSQL

INSERT .. ON CONFLICT UPDATE
functionality. Ideally I would be able to distinguish between which rows were successful inserted and which were updated. Is there a way to do it?

Answer

You need an additional auxiliary column for this (updated in the example).

create table test (id int primary key, str text, updated boolean);
insert into test values (1, 'old', false);

insert into test values
    (1, 'new 1', false),
    (2, 'new 2', false)
on conflict (id) do
update set 
    str = excluded.str, updated = true
returning *;

 id |  str  | updated 
----+-------+---------
  1 | new 1 | t
  2 | new 2 | f
(2 rows)