postgresql - SQL concurrent inserts issue -

postgresql - SQL concurrent inserts issue -

i have relation y(k, v) k key. having concurrent connections on database, run on each of them:

insert y select 'k1', 'v1' not exists (select 1 y k = 'k1');

sometimes next error:

error: duplicate key value violates unique constraint "y_pk"

why happen? can avoided?

the issue here if query runs in 2 different connections @ same time not exists status true both (it's tested before insert) , both connections insert same value. need table lock or seek utilize script:

begin insert y(k,v) values ('k1', 'v1'); exception when unique_violation end;

also read similar topic: so:how do upsert...

sql postgresql