I’m looking for a method to perform an upsert on a Postgres table but with some complex logic applied to the update based on the existing column value. Essentially I think I need some sort of combination of:

INSERT INTO counts (id, counter) 
VALUES (1, 0)
ON CONFLICT (id) DO UPDATE 
  SET counter = excluded.counter + 1; 

that does a simple upsert and the following SELECT FOR UPDATE synchronization:

// Begin Transaction

curr_count = SELECT counter FROM counts FOR UPDATE; // READ counter but locks rows from concurrent modification

// Perform some complex logic in js on curr_count to determine new_count

INSERT INTO counts ($1), new_count; // WRITE

// End Transaction

but I don’t know how you can you can do the synchronized update only if the insert fails.

I’m using AWS Serverless v2 Postgres but I don’t think this makes any difference to the problem.

Thanks!