Is it possible to return the IDs of a multi row insert in SQL?
<p>With the statement below I can insert multiple rows into a table. If the tag exists already then nothing happens, and if it doesn't exist it creates the row.</p>

<pre><code>INSERT INTO tags (name) VALUES ('this'),('is'),('my'),('interest'),('list'),('running'),('pokemongo'),('fighting'),('eating') ON CONFLICT DO NOTHING;
</code></pre>

<p>Is is possible to return the ID of all these values whether or not they exist? </p>

<p>I'm using python psycopg2 with postgres 9.5.</p>

<p>This can be accomplished in one query using a WITH block (see PostgreSQL docs on <a href="https://www.postgresql.org/docs/current/static/queries-with.html" rel="nofollow">WITH</a> and <a href="https://www.postgresql.org/docs/current/static/sql-select.html" rel="nofollow">SELECT</a>). For example:</p>

<pre><code>WITH t1 (c1) AS ( VALUES ('this'),('is'),... ),
t2 AS (SELECT id FROM tags WHERE name IN (SELECT c1 FROM t1) ),
t3 AS (INSERT INTO tags (name) SELECT c1 FROM t1 ON CONFLICT DO NOTHING RETURNING id)
SELECT id from t2
UNION
SELECT id from t3;
</code></pre>

<p>You could also replace <code>ON CONFLICT DO NOTHING</code> with <code>WHERE c1 NOT IN (SELECT name FROM tags)</code>. This would work without relying on the conflict caused by a unique index.</p>

python sql postgresql psycopg2 postgresql-9.5