PostgreSQL IN operator with lots of values in an external file

I have a file with a lot of identifiers, one in each line. I also have a DB with a superset of those id's. I would like to query the DB only with the id's of interest. Is there a way to "import" an external file or do I have to copy all 300 values into the IN expression? This is a large db.

Sure, either,

• Use a CTE with a VALUES statement.

• Use a TEMP table with an index.

Here is an example with the CTE.

WITH t AS (
  SELECT * FROM ( VALUES
    (1),
    (2),
    (3)
  ) AS t(table_id)
)
SELECT * FROM t
JOIN myTable
  USING (table_id)
;

Only slightly more complex is getting them into a temp table which permits you to index it. You can also use the Foreign Data Wrapper create a FOREIGN TABLE if you have an external file depending on the format. Try it without an index, and copy it into a table and see if an index speeds it up.

StackExchange Database Administrators Q#164119, answer score: 5