Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do you print the result of a PostgreSQL query in CSV or TSV format from the command line?

Tags:

postgresql

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?

like image 552
dan Avatar asked Jun 29 '11 13:06

dan


3 Answers

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.

like image 70
Matthew Wood Avatar answered Oct 07 '22 19:10

Matthew Wood


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 
  • Unaligned output mode: -A
  • Use comma as field delimiter: -F ,
  • Do not read psqlrc: -X
  • Tuples only (no header/footer): -t
  • File containing SQL query: -f
  • Output file: -o
like image 30
Jason McVetta Avatar answered Oct 07 '22 19:10

Jason McVetta


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"
like image 28
Bohemian Avatar answered Oct 07 '22 18:10

Bohemian