Return id if a row exists, INSERT otherwise

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 iUNION  ALLSELECT 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?

I would suggest doing the checking on the database side and just returning the id to nodejs.

Example:

CREATE OR REPLACE FUNCTION foo(p_param1 tableFoo.attr1%TYPE, p_param2 tableFoo.attr1%TYPE) RETURNS tableFoo.id%TYPE AS $$  DECLARE  v_id tableFoo.pk%TYPE;  BEGIN    SELECT id    INTO v_id    FROM tableFoo    WHERE attr1 = p_param1    AND attr2 = p_param2;    IF v_id IS NULL THEN      INSERT INTO tableFoo(id, attr1, attr2) VALUES (DEFAULT, p_param1, p_param2)      RETURNING id INTO v_id;    END IF;    RETURN v_id:  END;$$ LANGUAGE plpgsql;

And than on the Node.js-side (i'm using node-postgres in this example):

var pg = require('pg');pg.connect('someConnectionString', function(connErr, client){  //do some errorchecking here  client.query('SELECT id FROM foo($1, $2);', ['foo', 'bar'], function(queryErr, result){    //errorchecking    var id = result.rows[0].id;        };});

Something like this, if you are on PostgreSQL 9.1

with test_insert as (   insert into foo (id, col1, col2)   select 42, 'Foo', 'Bar'   where not exists (select * from foo where id = 42)   returning foo.id, foo.col1, foo.col2)select id, col1, col2from test_insertunion select id, col1, col2from foowhere id = 42;

It's a bit longish and you need to repeat the id to test for several times, but I can't think of a different solution that involves a single SQL statement.

If a row with id=42 exists, the writeable CTE will not insert anything and thus the existing row will be returned by the second union part.

When testing this I actually thought the new row would be returned twice (therefor a union not a union all) but it turns out that the result of the second select statement is actually evaluated before the whole statement is run and it does not see the newly inserted row. So in case a new row is inserted, it will be taken from the "returning" part.