ON CONFLICT ... DO UPDATE SET fails with duplicate rows

user_1879838 0
Viewed 224

I have next query:

INSERT INTO loger(state, id, event_timestamp, other_event_timestamp)
VALUES 
   (1, 12, '2020-01-01T19:00:00.000Z', '2020-01-01T19:00:00.000Z'),
   (1, 12, '2020-01-01T19:00:00.000Z', '2020-01-01T19:00:00.000Z') 
    ON CONFLICT(id, event_timestamp) DO UPDATE SET state = excluded.state

During execution it fails with error:

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time

p.s. these data comes outside, so I should pass it to query as is.

postgresql sql
1 Answers

The error message is pretty clear. The ON CONFLICT clause refers to rows already in the table. This is explained in the documentation:

INSERT with an ON CONFLICT DO UPDATE clause is a “deterministic” statement. This means that the command will not be allowed to affect any single existing row more than once; a cardinality violation error will be raised when this situation arises. Rows proposed for insertion should not duplicate each other in terms of attributes constrained by an arbiter index or constraint.

(I added the highlighting.)

You can reduce this to one row with some manipulation in the query. For instance, the following inserts one arbitrary row:

INSERT INTO loger (state, id, event_timestamp, other_event_timestamp)
    SELECT DISTINCT ON (id, event_timestamp) state, id, event_timestamp, other_event_timestamp
    FROM (VALUES (1, 12, '2020-01-01T19:00:00.000Z', '2020-01-01T19:00:00.000Z'),
                 (1, 12, '2020-01-01T19:00:00.000Z', '2020-01-01T19:00:00.000Z') 
         ) v(state, id, event_timestamp, other_event_timestamp)
    ORDER BY id, event_timestamp
    ON CONFLICT(id, event_timestamp) DO UPDATE SET state = excluded.state;

You can also structure your code as two separate INSERTs.

user_11440350
Related