I'd like to execute a query from the shell (not in the interactive psql client) and have it print the CSV or TSV representation of the output to STDOUT. How do you do that with psql
or one of the PostgreSQL command-line tools?
If you are using PostgreSQL 8.2 or newer, use this for CSV:
psql -c "COPY (<select query>) TO STDOUT WITH CSV"
and this of TSV, with proper NULLs:
psql -c "COPY (<select query>) TO STDOUT WITH NULL AS ''"
The CSV form will properly quote any fields that contain the double-quote character. See the PostgreSQL documentation of your specific version for more details and options for COPY.
Starting from Bohemian's answer, I found these flags useful:
psql my_database -U myuser -A -F , -X -t -f /path/to/query.sql -o /path/to/output.csv
EDITED: Using -F
Use commas via -F
and use "unaligned table output mode" -A
:
psql my_database -U myuser -A -F , -c "select * from mytable"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With