Search code examples
postgresqlshellcsvexport-to-excelexport-to-csv

How to export a PostgreSQL query output to a csv file

I'm having problem exporting my PostgreSQL output from a shell to a csv file.
My SQL script is called script.sql.

I typed the following command in my shell: 

psql congress -af script.sql &> filename.csv

But when I opened the filename.csv file, values of all the columns are squeezed in one column in the Excel csv (see the attached screenshot).

Then I tried another way. I edited my script.sql to be:

Copy (Select * From ...) To '/tmp/filename.csv' With CSV;

Then I typed the following command in the shell within the database dbname.

\i script.sql

The output is:

COPY 162

Well, my output query has 162 rows.

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?

Or, if I'm going to use the filename.csv (screenshot is attached), how can I fix the format of that csv/Excel file?

Screenshot of filename.csv

Solution

  • 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: