How to Export Data from PostgreSQL into a CSV File

Do you need to quickly send data to a client or share a report for further analysis? A CSV file is a great sharing option! Let’s take a look at how you can use this format to export data from a PostgreSQL database.

In this article, we will first review what the CSV file format is and why it’s handy when exporting your PostgreSQL database. Then we will export some example data from a real database using psql on the command line and pgAdmin, a free and open-source interface for PostgreSQL databases.

Before we get started, make sure you have set up your PostgreSQL database. If you haven’t yet worked with PostgreSQL, check out our SQL Basics in PostgreSQL course. It includes 130 interactive coding challenges designed to get you started on your Postgres journey. But if all you need is a quick refresher, here’s a way to practice your PostgreSQL skills.

What Are CSV Files?

CSV is short for Comma-Separated Values. This file format lets you store data in plain text, which makes it great for sharing data across applications.

A CSV file contains rows of data corresponding to individual records; the first row is usually the column names. The values in each row are separated by a comma. Although commas are the most commonly used separator (as the name of the format suggests), other characters (e.g. semicolons, tabs, or spaces) can also separate the values.

Here’s an example of a CSV file. We will export one just like it in the next sections:

store_id,revenue,day
1,100.42,2023-05-01
1,148.89,2023-05-02
2,238.98,2023-05-03

As you can see, the first column stores the store ID, the second column stores the revenue, and the third column stores the date. Each row is one record. It’s organized a lot like spreadsheets and databases, but it’s all in text!

Why Export a Database as a CSV File?

CSV files can be opened by almost any data-oriented software. Exporting your PostgreSQL data to a CSV file means you can easily share information with colleagues, even if they use different tools.

The CSV format is also native to many data analysis tools and spreadsheets. Exporting via this format lets you quickly and easily transfer data for in-depth analysis, visualization, and reporting.

Exporting Data from PostgreSQL to CSV

The Data

This will be our example table called sales. Each row represents how much each store earned on a given day.

sales

Exporting Data with SQL Queries

We will be using two different SQL queries to test different export methods. (It pays to have our PostgreSQL Cheat Sheet nearby for a productivity boost when you’re writing your own queries.) This is the first one:

SELECT * FROM sales

This first query is a simple one that selects everything in the table. The idea is to make sure we don’t lose data during the export.

Here is the second (much more complex) query:

WITH max_revs (store_id, day, revenue, max_rev) AS (
	SELECT
		store_id,
		day,
		revenue, 
		MAX(revenue) OVER(PARTITION BY day) 
	FROM sales 
)
SELECT
	store_id,
	day,
	max_rev
FROM max_revs
WHERE revenue = max_rev;

The second query simulates a simple report; for each day, it outputs the top store by revenue and the highest revenue itself. This is what you might write while working in data analysis. We use a common table expression to first select the highest revenue for the day; the outer (second) query selects the store(s) that match the revenue returned by the CTE.

If you are not yet familiar with CTEs and window functions, check out SQL Reporting track and our course on Window functions in SQL. But for now, let’s get back to exporting data from PostgreSQL to a CSV file. Once we have the data we want, we need to start the export process.

Exporting the Database Using the Command Line

First, let’s explore exporting a PostgreSQL database through the command line. You have two options here: using the \copy command or the COPY statement; we’ll explain the differences between the two in a moment.

While using either of these methods is less straightforward than using pgAdmin, you probably already have everything you need for it. So, let’s get started.

Connecting to the Database with psql

For both command line options, we’ll be using psql. This is a command line tool for PostgreSQL databases.

To connect to the database, enter the following command:

psql -h <hostname> -p <port> -d <database name> -U <username>

Replace hostname with the host name (or address) of the database. The port is specified on the remote server together with the database name and the username. After this, you will be prompted for a password.

Once you’re connected to the database, you can use either the \copy command or the COPY method to export your data. We’ll begin by demonstrating \copy.

1.     Exporting Data Using \copy

The \copy command will directly copy your local query result to a local file on your machine. It works on the client side, so it’s a great choice if you only have database read permissions.

Here’s the syntax:

\copy (query) to ‘filename’ with cvs [header]

You can include the optional header argument when you want the generated table to have an extra row at the top containing all the column names. To ensure the clarity of the results, I will use this argument in all future commands.

Let’s look at a couple of examples.

Example 1: Copy the Whole Table

Code:

\copy (SELECT * FROM sales) to ‘/dbExport/sales_full_table.csv’ with csv header

Explanation: After executing the query SELECT * FROM sales, your computer copies the results to the specified CSV file. No additional requests to the database are sent.

Example 2: Copy Query Results

Code:

\copy (WITH max_revs(store_id, day, revenue, max_rev) AS (SELECT store_id, day, revenue, MAX(revenue) OVER(PARTITION BY day) FROM sales) SELECT store_id, day, revenue FROM max_revs WHERE revenue = max_rev) to ‘/dbExport/sales_report_query.csv’ with csv header

Explanation: The same principle is at work here. The database executes this report query and your local computer copies the results into a file.

The \copy command is easy to use – and in most cases, it’s as fast as the average query. This is because the resulting file is generated locally instead of being sent over from the database.

2.     Using the COPY Statement

Unlike the \copy command, the COPY statement runs on the database side. We call this one a statement because COPY is actually part of a command executed by the database. That means that the resulting file will be saved on the remote server, so keep this in mind when choosing the file path. Saving to a remote server also means that you should have PostgreSQL superuser (root) privileges.

The syntax for the COPY statement looks like this:

COPY { table | (query) } 'filename' [ DELIMITER 'delimiter' ] [HEADER]

I have omitted some arguments for simplicity; if you want to see the full syntax, visit the PostgreSQL documentation

Example 1: Copy the Whole Table

Code:

COPY sales TO ‘/dbExport/sales_full_table.csv’ DELIMITER ‘,’ CVS HEADER

Explanation: As you’d expect, this copies the entire sales table to the sales_full_table.csv file. Note that we’ve set the delimiter to comma and included the header row.

Example 2: Copy the Query Results

Code:

COPY (WITH max_revs(store_id, day, revenue, max_rev) AS (SELECT store_id, day, revenue, MAX(revenue) OVER(PARTITION BY day) FROM sales) SELECT store_id, day, revenue FROM max_revs WHERE revenue = max_rev) TO ‘/dbExport/sales_full_table.csv’ DELIMITER ‘,’ CVS HEADER

Explanation: As the subtitle suggests, this exports the results of the query to the sales_full_table.csv file. Here’s an example of the data in table form:

As you can see, the result is the same as the \copy command; the only difference is the place where the resulting file is stored.

Exporting Data Using pgAdmin

Now let’s take a look at the second option, which uses a graphical user interface (GUI). You might already be familiar with pgAdmin; it’s a free and open-source PostgreSQL database management program. This is a simple and straightforward way to export data from a Postgres database.

Connect to the Database

Open pgAdmin and click Add New Server.

On the General page, choose a name for the connection.

On the Connection page enter the hostname (“localhost” if you are running the database on your own computer), name of the database, username, and password.

Finally, click Save. If the connection was established correctly, your dashboard page will now display graphs of your database’s activity.

Export Query Results

We’ll first open the query tool by either clicking the database icon (three disks) at the top toolbar or by using Alt+Shift+Q. You can now query the database by writing queries in the text box and executing them using either the play button at the top or the F5 key.

Once you execute a query, its result will be displayed in the bottom window. To save the result to a CSV file, press the download button just above the resulting data.

As you can see, the results are the same as with the command line tools. The main difference is that pgAdmin makes switching between designing queries and saving the results much easier.

Learn More About Exporting Data with CSV Files

Exporting databases to CSV files is a pleasure! Now that you know how to export all the data you need, you might have another question: How do I import data into a Postgres database? Read this short article on how to import data to PostgreSQL using pgAdmin to find out.

And if you are looking to expand your knowledge with PostgreSQL, check out our full A to Z with PostgreSQL track, which includes over 1,000 interactive exercises. Happy learning!