Postgres output query to file
Snippet
Written with a loving hand by kitt some time around 12:34 on 1 September 2019
Ways to output the results of a Postgres query to a file:
- 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
- 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.
SELECT id, name FROM person WHERE name like '%smith%' \g | cat >> '/tmp/smiths.txt';
Add new comment