sql - Return id if a row exists, INSERT otherwise

Question

I'm writing a function in node.js to query a PostgreSQL table.
If the row exists, I want to return the id column from the row.
If it doesn't exist, I want to insert it and return the id (insert into ... returning id).

I've been trying variations of case and if else statements and can't seem to get it to work.

Answer 1

A solution in a single SQL statement. Requires PostgreSQL 8.4 or later though.
Consider the following demo:

Test setup:

CREATE TEMP TABLE tbl (
  id  serial PRIMARY KEY
 ,txt text   UNIQUE   -- obviously there is unique column (or set of columns)
);

INSERT INTO tbl(txt) VALUES ('one'), ('two');

INSERT / SELECT command:

WITH v AS (SELECT 'three'::text AS txt)
    ,s AS (SELECT id FROM tbl JOIN v USING (txt))
    ,i AS (
       INSERT INTO tbl (txt)
       SELECT txt
       FROM   v
       WHERE  NOT EXISTS (SELECT * FROM s)
       RETURNING id
       )
SELECT id, 'i'::text AS src FROM i
UNION  ALL
SELECT id, 's' FROM s;

• The first CTE v is not strictly necessary, but achieves that you have to enter your values only once.

• The second CTE s selects the id from tbl if the "row" exists.

• The third CTE i inserts the "row" into tbl if (and only if) it does not exist, returning id.

• The final SELECT returns the id. I added a column src indicating the "source" - whether the "row" pre-existed and id comes from a SELECT, or the "row" was new and so is the id.

• This version should be as fast as possible as it does not need an additional SELECT from tbl and uses the CTEs instead.

To make this safe against possible race conditions in a multi-user environment:
Also for updated techniques using the new UPSERT in Postgres 9.5 or later:

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