Doing An Idempotent Insert With Postrgres

I'd like something like INSERT VALUES(1,2,3) INTO sometable ON CONFLICT DO NOTHING IF EXACTLY SAME ROW So I'd like The following behavior: #CREATE TABLE sometable (a int primary k

Solution 1:

You can use an INSERT based on a select:

insertinto sometable
select*from ( values (1,2,3) ) as data(a,b,c)
wherenotexists (select*from sometable
                  where data = sometable);

Yes, the condition where data = sometable is valid in Postgres and simply compares all columns.

This can also be extended to multiple rows:

insertinto sometable
select*from ( 
  values 
    (1,2,3),
    (4,5,6),
    (7,8,9)
) as data(a,b,c)
wherenotexists (select*from sometable
                  where data = sometable);

This does not prevent PK violation errors (as on conflict does) if done from multiple transactions though. You still need to handle those errors.