current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 3 down vote favorite

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.

share|improve this question
    
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 41 mins ago
up vote 4 down vote

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
share|improve this answer
up vote 4 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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