Delete rows of a table specified in a text file in

问题:

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?

回答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.