7

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
  • 605,456
  • 145
  • 1,078
  • 1,228
ptikobj
  • 2,690
  • 7
  • 39
  • 64
  • 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

9

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;  -- optional

Or use the psql meta-command \copy. The manual:

COPY naming a file or command is only allowed to database superusers or users who are granted one of the roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program, since it allows reading or writing any file or running a program that the server has privileges to access.

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

For repeated use, wrap it into a PL/pgSQL function with file-path / table name / column name as parameters. If any identifiers are dynamic you must use EXECUTE for the DELETE.

If you work with \copy, you have to do that in psql in the same session before executing SQL commands (possibly wrapped in a server-side function).

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
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