Export PostgreSQL into CSV

Redirect console output

First simple way is to redirect the psql console output to file:

\o '/tmp/file.csv'
SELECT * FROM users;

All the result output is redirected to the /tmp/file.csv. and then you open the csv and select the | character as the delimiter.

The obvious problem with this approach is that you cannot have | character in your data. and you also get some other text you dont want, like the result row count: (x row)

Export data into CSV format with header

Postgres has one function called COPY:

COPY (SELECT * FROM users) TO '/tmp/file.csv' CSV HEADER;

In this way, you can have a nicely escaped data with a nice header.