CODEWITHSUNDEEP
sqlpostgresql
Ted
Ted

Reputation: 1760

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

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.

Upvotes: 3

Views: 1171

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

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.

Upvotes: 5

Related Questions