I have a sql statement with ON CONFLICT ON CONSTRAINT in it to allow upserting. This seems to be working well except that my error logs have a bunch of errors related to this query.
I am confused by the error logging because we expect to run into this constraint and then just do the update – is there something broken here or is this just chatty logging?
RDS Postgres, version 12.7.
2021-12-18 16:00:32 UTC:172.31.1.154(33952):{username}@{database}:[28367]:ERROR: duplicate key value violates unique constraint "segments_sequence_number_event_id"
2021-12-18 16:00:32 UTC:172.31.1.154(33952):{username}@{database}:[28367]:DETAIL: Key (sequence_number, event_id)=(2, d5c70xxxx63478) already exists.
2021-12-18 16:00:32 UTC:172.31.1.154(33952):{username}@{database}:[28367]:STATEMENT: INSERT INTO segments (id,created_at,updated_at,event_id,sequence_number,start_time,end_time,bitrate,width,height) VALUES ('8e6d5xxxxbdae3','2021-12-18T16:00:32.596Z','2021-12-18T16:00:32.596Z','d5c70xxxx63478',2,1639843218000,1639843221000,2097152,1920,1080) ON CONFLICT ON CONSTRAINT sequence_number_event_id_unique DO UPDATE SET updated_at='2021-12-18T16:00:32.596Z',start_time=1639843218000,end_time=1639843221000,bitrate=2097152,width=1920,height=1080 RETURNING id,sequence_number,url,start_time,end_time,duration,bitrate,width,height,size,event_id,created_at,updated_at
>Solution :
Your ON CONFLICT clause uses ON CONSTRAINT sequence_number_event_id_unique
But the unique violation is raised by different constraint: unique constraint "segments_sequence_number_event_id"
To catch any and all conflicts, you can use ON CONFLICT DO NOTHING.
ON CONFLICT ... DO UPDATE can only catch a single "conflict target" (a single constraint, index, or index expressions).
Related:
Aside: You don’t have to pass values twice. You can just reuse values from excluded rows with:
...
SET (updated_at,start_time,end_time,bitrate,width,height)
= (EXCLUDED.updated_at,EXCLUDED.start_time,EXCLUDED.end_time,EXCLUDED.bitrate,EXCLUDED.width,EXCLUDED.height)
...
See:
Corner-case difference: Values from the EXCLUDED row include changes from possible triggers ON INSERT (which is typically desirable).