How to export a PostgreSQL query output to a csv file
Modern syntax:
COPY (SELECT * FROM ...) TO '/tmp/filename.csv' (FORMAT csv);
So the 162 rows of my output table have been copied in the shell. How can I paste or move them to a csv file?
The result is the CSV file. Open it with any spreadsheet program using matching delimiters. The manual:
The default is a tab character in text format, a comma in CSV format
The psql meta command \copy
is a wrapper around the SQL COPY
function. It writes and reads files local to the client (while COPY
uses files local to the server) and does not require superuser privileges.
See:
- Export specific rows from a PostgreSQL table as INSERT SQL script
- PostgreSQL: export resulting data from SQL query to Excel/CSV
Most previos answers are correct, here is more details and only for linux
1.Create a file like my_query.sql
\COPY (select * from some_table) TO '/tmp/some_file.csv' (format CSV);
2.Run this below command after you have updated the variables for your DB and file path.
psql "host=YOUR_HOST port=YOUR_PORT dbname=YOUR_DB user=YOUR_USER password=YOUR_PASS" -af my_query.sql
tip: be cautious of your free disk space you have available if you do a select * from your_table you might run out of space if have a large table.