How to Store PostgreSQL Output to File

By default, PostgreSQL displays query results in the terminal or command prompt. But many times you may need to store PostgreSQL output to file such as CSV or JSON file. This may be a result of a specific SQL query or an entire table itself. In this article, we will learn how to store PostgreSQL output to file.

How to Store PostgreSQL Output to File

There are two ways to do this. You can run the query to export data to file, on server-side or client-side. We will look at both these methods.

1. Server Side

If you want to export Query result to file on PostgreSQL server, then you can log into PostgreSQL and run the following command. Replace query in bold as per your requirement. Also replace /tmp/test.csv with the file path of output file.

Copy (Select * From table_name) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

If you want to export the above query’s output as tab delimited file instead of CSV file, just change the delimiter as shown below.

Copy (Select * From table_name) To '/tmp/test.txt' With CSV DELIMITER '\t' HEADER;

Also the above query will export the query headers along with query result. If you don’t want to export header, omit HEADER keyword from above query.

Please note, the above command will not be able to write to your local filesystem, in case you are connected to a remote server. Also, you need to be logged in as superuser in order to be able to run the above command.

No matter from which remote client you run this command, it will write to a file only on server’s filesystem.

2. Client Side

You can also log into local/remote PostgreSQL server and run the following command to export query result from client side itself. In this case, no matter from where you run this command, local or remote client, the output file will be written only on local filesystem. It is similar to the above command, except you need to use \copy instead of Copy.

\copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER

The above query takes the same arguments like \Copy mentioned above but runs the query on your local client machine.

Here also if you need to generate a tab delimited file, replace ‘,’ delimiter with ‘\t’ delimiter.

\copy (Select * From table_name) To '/tmp/test.txt' With CSV DELIMITER '\t' HEADER;

Also if you want to omit column headers from result, omit HEADER keyword.

In this article, we have learnt how to export SQL query result to file in PostgreSQL. You can use it to export results of specific SQL query, or entire table.

How to Store PostgreSQL Output to File