codes, bugs & demons

What happens when your collegue absolutely needs that CSV export from a Postgresql table and you have no GUIs or tools to export the data? psql is there for you.

[UPDATE] Thanks to Reddit user @MonCalamaro for improving this scripts with his suggestions.

psql -U root -d postgres \
-c "\copy (select * from schema_name.table_name) To STDOUT With CSV;" >> export.csv

Got a complex query? No problem, you can use a text file to read the query statement:

psql -U root -d postgres \
-f my_complex_query.sql >> export.csv

Example of my_complex_query.sql:

\copy (select id, name, created_at from schema_name.table_name where id=7) To STDOUT With CSV;

Finally, this is what you need to know about the flags we used:

-U, --username=root    database user name (default: "root")
-d, --dbname=root      database name to connect to (default: "root")
-c, --command=COMMAND  run only single command (SQL or internal) and exit
-f, --file=FILENAME    execute commands from file, then exit

Enjoy your exports.