Postgresql – Select columns by file containing primary keys

postgresqlpsql

I have a text file with primary keys. Each line contains one primary key. Now I want to select the rows based on the input file. I can not find the right option for psql to do this. What is the canonical way for PostgreSQL to do this job?

I know I can do

psql -c 'select * from table' db | grep -f primary_keys

But this dumps the whole database. How can I avoid it?

Best Answer

This assumes you are on a shell and have the tr command in the PATH:

psql -c "SELECT * FROM mytable WHERE id IN (`tr '\n' , <file`NULL)"

Every newline character is replaced with ,, and the NULL in the end takes care of the final comma.

Related Question