DigitalDisaster DigitalDisaster - 3 months ago 12
Python Question

Is it possible to return the IDs of a multi row insert in SQL?

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.

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


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

I'm using python psycopg2 with postgres 9.5.

ASL ASL
Answer

This can be accomplished in one query using a WITH block (see PostgreSQL docs on WITH and SELECT). For example:

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;

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

Comments