Delete rows of a table specified in a text file in Postgres

Question

I have a text file containing the row numbers of the rows that should be deleted in my table like this:

3
32
40
55
[...]

How can I get a PostgreSQL compatible SQL statement which deletes each of these rows from my table using the text file?

Answer 1

Doing it once could look like this:

CREATE TEMP TABLE tmp_x (nr int);

COPY tmp_x FROM '/absolute/path/to/file';

DELETE FROM mytable d
 USING tmp_x
 WHERE d.mycol = tmp_x.nr;

DROP TABLE tmp_x;

For repeated use, wrap it into a plpgsql function with file-path / table name / column name as parameters. If table or column name are dynamic you have to use EXECUTE for the DELETE.

Answer 2

I have a slightly different solution than Erwin's. I would use IN because performing a JOIN (USING) it would increase the number of rows that the query will process.

CREATE TEMP TABLE tmp_x (nr int);

COPY tmp_x FROM '/absolute/path/to/file';

DELETE FROM mytable d
 WHERE d.mycol IN (SELECT nr FROM tmp_x);

DROP TABLE tmp_x;