Suppose we have some table create table cnt( usr_id int primary key, usr_doc_ref text not null, cnt int, sum int );
And going to run some insert on conflict update on it (pgbench script): \setrandom id 1 50 insert into cnt as c(usr_id,usr_doc_ref, cnt) values(:id, '#'||:id, 1) on conflict(usr_id) do update set cnt=c.cnt+1; Run it: pgbench -c 16 -j 2 -t 50000 -n -h localhost -p 5432 -U postgres -f upsert2-ok.pgb work transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 16 number of threads: 2 number of transactions per client: 50000 number of transactions actually processed: 800000/800000 latency average: 0.000 ms tps = 36475.782816 (including connections establishing) tps = 36483.759765 (excluding connections establishing) All ok. Then add a unique constraint to the table. alter table cnt add constraint usr_doc_ref_uq unique(usr_doc_ref) Run pgbench again: pgbench -c 16 -j 2 -t 50000 -n -h localhost -p 5432 -U postgres -f upsert2-ok.pgb work client 2 aborted in state 2: ERROR: duplicate key value violates unique constraint "usr_doc_ref_uq" DETAIL: Key (usr_doc_ref)=(#39) already exists. client 6 aborted in state 2: ERROR: duplicate key value violates unique constraint "usr_doc_ref_uq" DETAIL: Key (usr_doc_ref)=(#16) already exists. client 9 aborted in state 2: ERROR: duplicate key value violates unique constraint "usr_doc_ref_uq" DETAIL: Key (usr_doc_ref)=(#28) already exists. So, if we have primary key and unique constraint on a table then upsert will not work as would expected.
