It’s surprisingly complicated to collect a bunch of row IDs and then delete the rows en bloc in SQL. I have found a nice way with a recent version of Python’s Postgres driver psycopg3.

An obvious approach would be1:

ids_to_delete = [25, 3, 80, 42]
cursor.execute("DELETE from tbl WHERE id IN %s;", (ids_to_delete,))

However, that doesn’t work. You can’t pass a sequence to a WHERE IN clause.

A cursory Google search is unhelpful and leads to solutions that either are prone to SQL injection or plainly break the moment the identifiers aren’t integers (but, say, UUIDs).

The best approach I’ve found is utilizing psycopg3’s new Cursor.copy() context manager together with a temporary table:

cursor.execute("CREATE TEMP TABLE to_delete (id int);")
with cursor.copy("COPY to_delete (id) FROM STDIN;") as copy:
    for id_ in ids_to_delete:

    id IN (SELECT id FROM to_delete);""")
  1. You create one big batch file that you then load into the temporary table to_delete with one command.
  2. You refer to that table using an IN clause of your DELETE statement.
  3. The temporary table is automatically cleaned up for you.

Of course, that workflow is useful for many other problems, too!

  1. Psycopg uses %s for passing arguments to SQL queries – relax. ↩︎