Sometimes we need to save output of our queries to a CSV file format, may be with a different delimiter. In this article we are going to cover how to save psql output to a file.

TL;DR: Copy (My amazing query) to /path/to/file.csv with CSV DELIMITER ‘,’ HEADER;

In TL;DR section i mentioned only one way, but of course answer depends if you are doing it on server side or client side. In most of cases, we don’t grant business users to database servers, they can not ssh to server, therefor they can access the output file.

Saving Output on Server Side

As mentioned in TL;DR section you can use following query:

Copy (SELECT * FROM amazing_table) to /path/to/file.csv with CSV DELIMITER ',' HEADER;

This command will create a file on /pat/to/file.csv, with a header with column names. You can directly use it on Excel or Calc.

If you don’t need headers, you can remove HEADER keyword from query.

If you want to learn more about COPY statement, read more about it from this link.

Saving Output on Client Side

In cases, users can not access to server, psql client can help them to create CSV file on their local workstations.

Like other client side commands, and as you can guess, \copy is the command to achieve this. This method also similar with server side, only usage of copy keyword changes:

\copy (SELECT * FROM amazing_table) to /path/to/file.csv with CSV DELIMITER ',' HEADER;

That simple!

Since PostgreSQL is an open source tool, it’s developers are focused on user experience as much as they focus on performance and technology.

Two methods can be used to save output from PostgreSQL to a csv file.

Please leave a comment below if you have any questions or any comments.