Postgres output query to file

Ways to output the results of a Postgres query to a file:

1. Use the command line switch options -c to input the query and -o to redirect the output to a file:
   

   % psql -U user -W -c "SELECT id, name FROM person WHERE name like '%smith%'" -o smiths.txt database 
   

2. Use the query construct \g 'filename' to write the results to file from inside the Postgres command line interface:
   

   % psql -U user -W database
   database=> SELECT id, name FROM person WHERE name like '%smith%' \g '/tmp/smiths.txt' ;
   Query OK, 10 rows affected (0.01 sec)
   

   If you want to add to an existing file, use cat:

   % psql -U user -W database
   database=> SELECT id, name FROM person WHERE name like '%smith%' \g | cat >> '/tmp/smiths.txt' ;
   Query OK, 10 rows affected (0.01 sec)
   

See also, the mysql versions.

Be sure where you have write permissions where the output file will be written to. Specify the full path if in doubt.