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 (
FROM generate_series(1,10) AS t(x)
t2 AS (
INSERT INTO foo (bar)
WHERE NOT EXISTS (
WHERE foo.bar = x
RETURNING foo, bar
SELECT foo, bar
INNER JOIN t1 ON (foo.bar = t1.x)
- Generate test data in a cte
t1 which we plan to
INSERT the data we don't already have into our desired table...
foo, and return the results (that which was inserted) as
- We run a
SELECT to get all of the elements in our test data (
t1) that already exist.
INSERT all of the others.