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.
