Export a PostgreSQL query to a CSV

Export the contents of a database table to a CSV file using the psql PostgreSQL utility in PuTTY.

If you need to export data from a PostgreSQL database in psql, there is a fairly easy way to do this wouthout requiring superuser privelleges.

When using psql, the general method for querying data is to log into a database in PuTTY:

psql Database_Name Username

Then enter a command in the following format:

SELECT * From Table_Name WHERE Column_Name LIKE 'Foo%';

In this example, we want to look into a Table called “Table_Name” and search everything (*) in the column “Column_Name” for entries that contain “Foo” at the start (hence the % wildcard). There are many variations of this SELECT function. This is beyond the scope of this article.

If we wanted to send all of the results from our database query to a CSV file, we can do this via the \COPY command:

\COPY (SELECT * From Table_Name WHERE Column_Name LIKE 'Foo%') TO 'csv-output/csv_file.csv' WITH CSV DELIMITER ',' HEADER

What this says is that we want to copy the output from our query to a CSV file called “csv_file.csv” on our server in a folder called “csv-output”. We have specified a comma as the delimiter and want the table headers included. You will notice that we need to exclude the trailing semicolon in our database query for this command.

The \COPY command (as opposed to COPY) performs a frontend (client) copy operaton. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server and no SQL superuser privileges are required. (psql actually initiates the STDCOPY function when using COPY).

The question then arises; where specifically is the CSV file?

To answer that question, we need to see where STDCOPY is placing it, which is the current working directory. To determine the current working directory in psql, enter the following command:

\! pwd

This is the location where the CSV file will be found (or the /csv-output/ subdirectory in our case). The file can then be accessed and downloaded via WinSCP or another FTP programme.

Please note that if you are running PostgreSQL on a Windows server, file paths will need to utilise a double-backslash.

Comments

2 responses to “Export a PostgreSQL query to a CSV”

On 4 June 2019, 1984 wrote:

Thank you so much

everytime with a select that has say 40 rows i get a “parse error at end of line when i try to copy the script in

So with putty i typed the following

\copy (right click to paste 40 line script in) TO ‘1984.corporatestoogeserver/ReallyImportantJobGivenToPoorlyTrainedGuy.csv WITH CSV DELIMITER ‘,’ HEADER

it never bloody works. What am i doing wrong??

On 19 June 2020, Shane O. wrote:

Looks like you’re missing a single quote following your filename.

Export a PostgreSQL query to a CSV

Comments

Have Your Say