Postgres ON CONFLICT ON CONSTRAINT triggering errors in the error log-CodePudding

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

CodePudding user response：

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).

How to use RETURNING with ON CONFLICT in PostgreSQL?

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:

How to UPSERT multiple rows with individual values in one statement?

Corner-case difference: Values from the EXCLUDED row include changes from possible triggers ON INSERT (which is typically desirable).