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).– jpmc26Feb 13, 2017 at 6:42
-
@jpmc26: 60 million :S– Dervin ThunkFeb 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?– jjanesFeb 13, 2017 at 16:43
-
@DervinThunk do these answers satisfy the question? Can you mark one as chosen if so?– Evan CarrollFeb 14, 2017 at 21:13
Add a comment
|
2 Answers
Sure, either,
- Use a CTE with a
VALUESstatement. - Use a
TEMPtable 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.
answered Feb 12, 2017 at 20:23
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
