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?
Add a comment
|
8 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.
2 Comments
Scott Marlowe
It really does make this stuff easy, don't it? Imagine the answer from some of the "big database vendors"... First install this 64 Meg package and then run this export tool that has the most arcane syntax imaginable. Be careful not to delete your whole database while using it. :)
dfrankow
Note: you can't get a header for TSVs this way, you need something like stackoverflow.com/a/57862424
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
1 Comment
Jon Carlson
This will not work as you might expect if you are outputting a text field with a comma in it.
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"
1 Comment
Red15
This will give problems if your separator character appears in values of the column.
To specify a tsv use the delimiter '\t'
psql my_database -U myuser -F'\t' --no-align -f mysqlfile.sql -o outputfile.tsv
To specify a csv use the delimiter ','
psql my_database -U myuser -F',' --no-align -f mysqlfile.sql -o outputfile.csv
1 Comment
Francesco Frassinelli
'\t' is not enough in Bash; --field-separator=$'\t' works as expectedAlso possible is the copy command which allows you to specify header, delimiters and quoting options
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"
Comments
You can specify the field separator with the -F command line parameter to psql
Comments
Export AS TSV WITH HEADER
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;
E.g. (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]$
