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. Per documentation:

The default is a tab character in text format, a comma in CSV format

Like Patrick commented, you can use the corresponding psql meta command \copy in a similar fashion. It writes (and reads) files local to the client and does not require superuser privileges.

More explanation in these related answers:

• Export specific rows from a PostgreSQL table as INSERT SQL script
• PostgreSQL: export resulting data from SQL query to Excel/CSV

first copy your connection info into ~/.pgpass and

cat ip:port:dbname:user:pass > ~/.pgpasschmod 0600 ~/.pgpasspsql -h serverip -U userid dbname -af test.sql | gzip > result.txt.gz

Going off on a bit of a tangent, there is another way too.

I use the following in a windows batch script: -

psql.exe -U %cUser% -h %cHost% -p %cPort% -d %cDB% -t -o "%dumpDir%\tables.txt" -A -c "SELECT table_schema, table_name   FROM information_schema.tables WHERE table_schema = '%qSchema%';"

The trick is to remember the -A option. It suppresses whitespace padding of the data.

I do this to avoid permission errors from the COPY command shown above with the account running postgress not have the same permissions as the account running my scheduled batch file.

This gets me a list of tables in a given schema with results like:-

myschema|mytable1

myschema|mytable2

I then use the FOR batch command to process each line. If you REALLY wanted a CSV file, all you would need to do would be this:-

ECHO table_schema,table_name > %dumpDir%\tables.csvFOR /F "delims=|" %%p in (%dumpDir%\tables.txt) DO echo %%p,%%q >> %dumpDir%\tables.csv

Probably not the most efficient of mechanisms, but works fine on small numbers of output rows.