3

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?

sumek
  • 26,495
  • 13
  • 56
  • 75
  • You need an additional auxiliary column for this. – klin Jul 27 '16 at 13:18
  • Using such functionality I can't see a way to have this information, as said by Klin, you would need a column to store which operation happened even with this you would need another thing to know that specific operation (because it can have many happening at same time). Best way I can see is to have a trigger with an audit table. The trigger would audit inserts and updates but even with a trigger you would need something to identify that operation to differentiate from the previous ones – Jorge Campos Jul 27 '16 at 13:23
  • There is a `RETURNING` clause for the insert command, but I would not know if it has the ability to distinguish between inserts and updates. See it here: [Insert](https://www.postgresql.org/docs/9.5/static/sql-insert.html) – Jorge Campos Jul 27 '16 at 13:24

2 Answers2

9

There is a way without adding a column to your table:

CREATE TABLE tbl(id int PRIMARY KEY, col int);
INSERT INTO tbl VALUES (1, 1);

INSERT INTO tbl(id, col)
VALUES (1,11), (2,22)
ON     CONFLICT (id) DO UPDATE
SET    col = EXCLUDED.col
RETURNING *, (xmax = 0) AS inserted;

Explanation:

Due to race conditions under concurrent write load, certain rows might not be returned. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

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)
klin
  • 112,967
  • 15
  • 204
  • 232
  • How would he differentiate the newly updated=true from the previous conflicts (from another inserts/updates)? That is why I didn't provide such answer. – Jorge Campos Jul 27 '16 at 13:31
  • No need to distinguish between old and new conflicts as you have results from `returning *` which are always up-to-date. – klin Jul 27 '16 at 13:40
  • Hmmm, didn't think of that. Thanks for the info. – Jorge Campos Jul 27 '16 at 13:43