Postgres: Insert if not exists, otherwise return the row

Question

In one SQL statement, I am trying to insert a row, and if that fails due to a constraint, then return the existing row.

I have:

INSERT INTO session (token, expiry) SELECT 'abcdefg', '2014-05-14 20:25:12.279667' WHERE  NOT EXISTS (SELECT * FROM session where token= 'abcdefg');

The token column has a unique constraint. I've tried appending RETURNING * at the end, but that still does not return the existing row.

Why is this? I thought my last SELECT statement will be executed and returned. Any ideas?

Note: I cannot use a Postgres function or multiple SQL statements due to some complicated race condition.

Answer 1

WITH d(t, e) AS ( VALUES ('abcdefg', '2014-05-14 20:25:12.279667')),
  t AS (SELECT token FROM session, d WHERE token=t),
  i AS (INSERT INTO session (token, expiry) 
           SELECT t, e FROM d WHERE t NOT IN (SELECT token FROM t))
SELECT t,e FROM d WHERE t IN (SELECT token FROM t);

The query first makes a CTE d with the data you want to insert

Then it makes a CTE t with the token in session that match the token in d (or empty if no match).

Then it uses CTE i to insert the row from d if it has no match in t into session.

And finally it returns the row from d if it did have a match in t.

BTW this also works with multiple rows.