How to properly call an "upsert" using parameterized raw SQL to Postgresql in ActiveRecord?
Statement, formatted readably:

WITH 
sel AS (
  SELECT id FROM categories WHERE name=$1 AND source=$2
),
ins AS (
  INSERT INTO categories (name, source, created_at, updated_at )
  SELECT  $1, $2, 'now', 'now'
  WHERE NOT EXISTS (SELECT * FROM sel) 
  RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM sel;


Dummy table:

CREATE TABLE categories (
    id serial not null PRIMARY KEY,
    name text not null,
    source text not null,
    created_at timestamp,
    updated_at timestamp,
    UNIQUE(name, source)
);


Demo, with three concurrent psql sessions:


Session1: PREPARE q(text,text) AS WITH ...
Session2: PREPARE q(text,text) AS WITH ...
Session0: BEGIN;
Session0: LOCK TABLE categories IN ACCESS EXCLUSIVE MODE;
Session1: EXECUTE q('n', 's');
Session2: EXECUTE q('n', 's');
Session0: ROLLBACK;


One session will report:

regress=&gt; EXECUTE q('n', 's');
 id 
----
  1
(1 row)


and one will report:

regress=&gt; EXECUTE q('n', 's');
ERROR:  duplicate key value violates unique constraint "categories_name_source_key"
DETAIL:  Key (name, source)=(n, s) already exists.


So it might be OK if you have a unique constraint on (name, source) and are willing to retry errors, but if you don't have that unique constraint:

ALTER TABLE categories DROP CONSTRAINT categories_name_source_key;


then you'll get a double insert, because your SELECT for IDs can run on both sessions before either session executes the INSERT.

If you have a unique constraint, there's no point wasting your time with all this hoop jumping. You're better off just running the insert and if it fails, querying for the ID of the existing row.