6

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?

Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
ptikobj
  • 2,630
  • 7
  • 36
  • 62
  • maybe useful link: http://stackoverflow.com/questions/3959692/rownum-in-postgresql HTH. – Thinhbk Oct 05 '11 at 09:38
  • 1
    Do you mean that you want to perform the delete automatically given such a text file, or do you just need to do it once? – Hugh Jones Oct 05 '11 at 10:32

2 Answers2

8

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.

Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
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;
Ulises
  • 21
  • 3