PostgreSQL: WHERE ID IN file_with_ids

I have a file with ids which looks like this:

10555
16452
17005
17511
17512
17513
17515
17516
17517
17518
17520
17521
17522
17523
17524
17525

This file was created with unix tools like grep and cut.

I would like to execute a SQL statement in PostgreSQL roughly like this:

SELECT * from my_table where id in file_with_ids;

Is there a simple way to do this?

As a_horse_with_no_name's suggestion, the first step you need a CSV file that stores ids. Then, import it to database and execute your query as below

CREATE TEMP TABLE tmp_list_ids (id int);
COPY tmp_list_ids FROM 'your absolute path here' CSV; 
SELECT * FROM your_table WHERE id IN (SELECT t.id FROM tmp_list_ids);

Please note that the COPY command is only allowed to superuser (bold is mine)


Files named in a COPY command are read or written directly by the
server, not by the client application. Therefore, they must reside on
or be accessible to the database server machine, not the client. They
must be accessible to and readable or writable by the PostgreSQL user
(the user ID the server runs as), not the client ...
COPY naming a file or command is only allowed to database superusers
or users who are granted one of the default 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.

StackExchange Database Administrators Q#227168, answer score: 3