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

user_232417 0
Viewed 52694

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?

postgresql
8 Answers

The simplest way (using psql) seems to be by using --csv flag:

psql --csv -c "SELECT * FROM sometable"
user_22959640

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]$
user_19049430
Related