Generate CSV file with psql cli using custom SQL queries
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.