Atomic Insert in PostgreSQL

A common problem is finding a record by a certain criterion or, if it doesn’t exist, inserting it. With operations like these, it’s important to ensure that multiple processes searching for the same record at roughly the same time don’t cause a race condition. Here’s a solution using a stored procedure for PostgreSQL. It avoids a race condition between the lookup and the insert while requiring only minimal locking:

CREATE OR REPLACE FUNCTION findOrCreate(
  IN criterion_in text, 
  OUT id int, OUT is_new bool) AS 
$$
DECLARE 
BEGIN
  LOCK TABLE my_table;
  SELECT false, id FROM my_table WHERE criterion=criterion_in INTO is_new, id;

  IF id IS NULL THEN
    INSERT INTO 
      my_table (criterion) 
    VALUES 
      (criterion_in) 
    RETURNING id INTO id;
    
    SELECT true INTO is_new;
  END IF;

  RETURN;
END
$$ language plpgsql;

Note the handy OUT parameters, which allow you to call this function as follows:

SELECT id, is_new FROM findOrCreate(criterion);

The return values are id and is_new, which tell you the database ID and whether the record was newly created.

The table lock is automatically released when the function exits (which is always implicitly a transaction).