How to Export PostgreSQL Data to a CSV or Excel File

Learn SQL

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;

Opening

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?


If you're finding this SQL Tutorial helpful, you may also enjoy our product Chartio. Chartio is on a mission to enable anyone in a company with the power of a data analyst. To do so we've built a very user friendly interface for people to explore and visualize data with either SQL, or our drag and drop Visual SQL. You can connect to all of your different data sources, and quickly create visuals and dashboards to share with your team. Give us a spin, there's a 2 week free trial.