Upsert with a transaction

Solution 1:

I wrote a rather large blogpost about it, so even though I might get downvotes for links, read this.

The gist is that transactions do not help in here (at least by default), and while it is possible to write correct upsert, it's actually pretty tricky.

Solution 2:

Assuming this simple table:

CREATE TABLE tbl(id int primary key, value int);

This function almost 100% secure (see comments) for concurrent transactions.:

CREATE OR REPLACE FUNCTION f_upsert(_id int, _value int)
  RETURNS void AS
$func$
BEGIN
LOOP
   UPDATE tbl SET value = _value WHERE  id = _id;

   EXIT WHEN FOUND;

   BEGIN
      INSERT INTO tbl (id, value)
      VALUES (_id, _value);

      RETURN;

   EXCEPTION WHEN UNIQUE_VIOLATION THEN     -- tbl.id has UNIQUE constraint.
      RAISE NOTICE 'It actually happened!'; -- hardly ever happens
   END;

END LOOP;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT f_upsert(2, 2);

It's very similar to this INSERT / SELECT case with more explanation and links:

• Is SELECT or INSERT in a function prone to race conditions?