2

''m using postgresql 9.5.

For one id I can query as follows:

select a,b from mytable where id = '1234' order by a,b;

Q1.
I have 50 unique id's of interest in a txt file. How do I run ^ query on all those 50 id's in one go?

(Overall there are about 100000 records in the mytable )

Q2.
If possible I'd also like to save the results recursively to different text files.

For 1 file I can do 

COPY "select a,b from mytable where id = '1234' order by a,b;" TO STDOUT CSVHEADER > '/tmp/1234.csv'

Ref: psql - save results of command to a file
Save PL/pgSQL output from PostgreSQL to a CSV file

Extra Info : I'm querying from my local machine.
The postgres is installed on AWS. (I have read access to the db).

kRazzy R
  • 1,453
  • 1
  • 13
  • 35
  • 1
    something like? `select a,b from mytable where id in (select id from table_created_with_50_other_ids) order by a,b;` – Jim Jones Apr 25 '18 at 16:12
  • ok thanks. this sounds like a good starting point. I must create a new table and load these 50 ids's into it and follow your tip. – kRazzy R Apr 25 '18 at 16:13

1 Answers1

1

You can import this text file of yours containing the ids into a temporary table and use it as a filter in a IN clause.

Consider a file containing these ids ...

1
3

.. and this table structure and data sample ...

CREATE TABLE t (id SERIAL, val TEXT);
INSERT INTO t (val) VALUES ('foo'),('bar'),('foo2'),('bar2');

SELECT * FROM t;
 id | val  
----+------
  1 | foo
  2 | bar
  3 | foo2
  4 | bar2

Import the ids into a temporary table: 

CREATE TEMPORARY TABLE tmp (id INT);
COPY tmp FROM '/tmp/ids.csv';

And use it in your query:

SELECT * FROM t WHERE id IN (SELECT id FROM tmp);
 id | val  
----+------
  1 | foo
  3 | foo2
(2 Zeilen)

Or in your export using COPY:

COPY (SELECT * FROM t WHERE id IN (SELECT id FROM tmp)) TO ... ;
Jim Jones
  • 9,620
  • 2
  • 21
  • 31