How to Export PostgreSQL Data to a CSV or Excel File

Last modified: February 07, 2021

PostgreSQL has some nice commands to help you export data to a Comma Separated Values (CSV) format, which can then be opened in Excel or your favorite text editor.

To copy data out first connect to your PostgreSQL via command line or another tool like PGAdmin.

Copying Full Tables

To copy a full table to a file you can simply use the following format, with [Table Name] and [File Name] being the name of your table and output file respectively.

COPY [Table Name] TO '[File Name]' DELIMITER ',' CSV HEADER;

For example, copying a table called albums to a file named /Users/dave/Downloads/albums.csv would be done with.

COPY albums TO '/Users/dave/Downloads/albums.csv' DELIMITER ',' CSV HEADER;

Note, PostgreSQL requires you to use the full path for the file.

Copying a Query Result Set

Besides exporting full tables you can also export the results of a query with the following format where [Query] and [File Name] are your query and output file name respectively.

COPY ([Query]) TO '[File Name]' DELIMITER ',' CSV HEADER;

For example, the following query exports all the blues (genre #6) tracks from a table.

COPY (SELECT * FROM tracks WHERE genre_id = 6) TO '/Users/dave/Downloads/blues_tracks.csv' DELIMITER ',' CSV HEADER;


After you have run the copy command you can then open the .CSV file(s) with Excel or your favorite text editor.

figure 1

Did you know, that you can also import data from CSV or Excel files into PostgreSQL?

Written by: Dave Fowler
Reviewed by: Matt David

Next – How to Replace Nulls with 0s in SQL

Get new data chapters sent right to your Inbox