Name for SELECT equivalent of UPSERT
In PostgreSQL we have a name for it INSERT ... RETURNING.

You can use this form in a CTE to obtain the effect you want..

CREATE TABLE foo ( foo serial, bar int );


WITH t1 AS (
  SELECT x
  FROM generate_series(1,10) AS t(x)
),
t2 AS (
  INSERT INTO foo (bar)
  SELECT x
  FROM t1
  WHERE NOT EXISTS (
    SELECT *
    FROM foo
    WHERE foo.bar = x
  )
  RETURNING foo, bar
)
SELECT foo, bar
FROM foo
INNER JOIN t1 ON (foo.bar = t1.x)
UNION ALL
  SELECT *
  FROM t2
;


Here we


Generate test data in a cte t1 which we plan to INSERT or SELECT on.
With t2 we INSERT the data we don't already have into our desired table... foo, and return the results (that which was inserted) as t2
We run a SELECT to get all of the elements in our test data (t1) that already exist.
INSERT all of the others.