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

Question

I would like to execute a request from the shell (not in the interactive psql client) and print the CSV or TSV representation of the output in STDOUT. How do you do this using psql or one of the PostgreSQL command line tools?

Answer 1

If you are using PostgreSQL 8.2 or later, use this for CSV:

 psql -c "COPY (<select query>) TO STDOUT WITH CSV" 

and this is TSV with corresponding NULL:

 psql -c "COPY (<select query>) TO STDOUT WITH NULL AS ''" 

The CSV form will correctly indicate any fields containing the double quote character. For more information and options for COPY, see the PostgreSQL documentation for your specific version.

Answer 2

Starting with the bohemian 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 

• Unexpressed output mode: -A
• Use a comma as a field separator: -F,
• Do not read psqlrc: -X
• Tuples only (no header / footer): -t
• File containing the SQL query: -f
• Output file: -o

Answer 3

EDITED: using -F

Use commas with -F and use the "unsigned table output mode" -A :

 psql my_database -U myuser -A -F , -c "select * from mytable" 

Answer 4

To specify tsv , use the delimiter '\ t'

 psql my_database -U myuser -F'\t' --no-align -f mysqlfile.sql -o outputfile.tsv 

To specify csv , use the delimiter ','

 psql my_database -U myuser -F',' --no-align -f mysqlfile.sql -o outputfile.csv 

Answer 5

Copy is also possible, which allows you to specify headers, separators, and quotation marks.

 psql my_database -U my_user -c "copy (select a.id,b.id from my_table_a as a inner join my_table_b as b on b.id = a.id) to STDOUT" 

Answer 6

You can specify a field separator with the -F command line switch in psql

Answer 7

Export as TSV with title

You can include the header as follows:

 \COPY (SELECT * FROM tca) TO '/.../metab/tca.tsv' WITH DELIMITER E'\t' CSV HEADER; \COPY (SELECT * FROM tca) TO '/...a/metab/tca.tsv' WITH NULL AS '' DELIMITER E'\t' CSV HEADER; 

For example, (PSQL):

 [metabolism]# \COPY (SELECT * FROM tca) TO '/mnt/Vancouver/programming/data/metabolism/tca.tsv' WITH NULL AS '' DELIMITER E'\t' CSV HEADER; COPY 22 

BASH:

 [victoria@victoria tsv]$ pwd /mnt/Vancouver/programming/data/metabolism/tsv [victoria@victoria tsv]$ head -n3 tca.tsv uuid src tgt rel rel_type 878b87de-0ca8-49a8-9f77-a24353e251d2 oxalosuccinic acid oxoglutaric acid 1.1.1.42 2 7fd9cf88-495b-491b-956e-294f19097923 isocitric acid oxoglutaric acid 1.1.1.41 2 [victoria@victoria csv]$