5

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.

4
  • Define "large." We've all come across people who think a few thousand rows is "large" even though most relational DBs can power through them like a hot knife through butter. =) It's much better to speak of actual sizes or, even better, rough row counts (with at least correct order of magnitude).
    – jpmc26
    Feb 13, 2017 at 6:42
  • @jpmc26: 60 million :S Feb 13, 2017 at 13:59
  • Who owns/controls the file? Is it under the same ownership/control as the database itself? How often does it change?
    – jjanes
    Feb 13, 2017 at 16:43
  • @DervinThunk do these answers satisfy the question? Can you mark one as chosen if so? Feb 14, 2017 at 21:13

2 Answers 2

5

Sure, either,

  1. Use a CTE with a VALUES statement.
  2. 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.

4

If you can run command from shell (better if it is Unix/Mac, but probably may be Windows), you can run something like this for integer/float/number:

echo "SELECT * FROM table WHERE field IN ("`cat < 000.file  | awk '{printf("%s,", $1)}' | sed 's/,$//g'`")" | psql db

Or something like this for varchar, timestamp, etc:

 echo "SELECT * FROM table WHERE field IN ("`cat < 000.file  | awk '{printf("_%s_,", $1)}' | sed 's/,$//g' | sed "s/_/\'/g"`")" | psql db

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.