How to Export Postgres to a CSV file

Introduction

When you’re working with data in a PostgreSQL database, you may need to export your data into a particular file format. One commonly-used format is a comma-delimited, or CSV, file. In this article, we’ll show you how to export Postgres to a CSV file using the \COPY command.

Prerequisites to using PostgreSQL

We’ll be executing some commands in PostgreSQL during this tutorial, so it’s important to go over the prerequisites that need to be in place for the task:

• First, make sure that you have PostgreSQL installed on your device. To check if the service is installed and running, use the command service postgresql status. This command will indicate if the status is active.

• You’ll also need to make sure psql is installed. You can use the command psql -V to verify that this command-line interface for PostgreSQL is installed and working properly.

Accessing PostgreSQL using the ‘psql’ Command-line Interface

In order to follow along with this tutorial, you’ll need to access a PostgreSQL database. You can use the following commands to connect to PostgreSQL and access a particular database on your localhost server:

When you execute this command, you’ll be prompted for a password. Once you enter the password and press RETURN, you should have access to PostgreSQL.

Next, type the following command to access your specified database:

You’ll be prompted for a password once again when you use this command.

Create a PostgreSQL Table to Export as a CSV File

Now that we have access to a PostgreSQL database, we’l need to connect to, or create, a PostgreSQL table. We’ll use this table to export data as a CSV file. The following SQL statement will create a PostgreSQL table that can be used for our examples:

Insert Records into the PostgreSQL Table

The next step will be to add some records to our table. The syntax for a typical INSERT statement looks like the following:

We can insert a record into our student table using the SQL statement shown below:

Let’s assume that we inserted a total of 10 records into our newly-created table. We can then display all these records using the SELECT statement:

We would get results that look like the following:

Export Records from a PostgreSQL Table into a CSV File

At this point, we’re ready to export the records of our PostgreSQL table into a CSV file. To do this, we’ll use the \COPY statement in the psql command-line interface. The DELIMITER clause lets us specify what character should be used to separate the values (typically, a comma is used). The CSV HEADER clause tells psql to use the PostgreSQL column names for the CSV header.

Construct a SQL Statement to Export Postgres Table Data to a CSV File

Let’s look at an example of how to use the \COPY command to export our student table data to a CSV file named student.csv:

NOTE: If you omit the backslash (\) before the COPY statement, you’ll get a warning that states: relative path not allowed for COPY to file.

Now, let’s see how to execute the same SQL statement with an absolute path specified along with the CSV filename:

You can also use relative paths in your \COPY command, but you’ll need to make sure to leave out the slash (/) at the beginning of the filename string to avoid getting a No such file or directory response.

The following example shows how you can export PostgreSQL table data as a CSV file into a directory with a relative path:

NOTE: The \COPY statement will create the CSV file in the same directory that you were in prior to entering the psql interface, unless you specify another path.

Verify that the PostgreSQL Table Data was Exported as a CSV File

Once you finish exporting your data, you can use \q to exit from the psql command-line interface. Then, use the cat command in the terminal to verify that the Postgres data was properly exported into the CSV file:

If you open the file, you should see all of your record data in comma-delimited format:

Conclusion

It’s common to need your PostgreSQL table data exported to a format such as a CSV file. In this tutorial, we created a sample table with several records, and we explained how to use the \COPY command to export PostgreSQL data to a CSV file. Using the example we provided as a guide, you’ll be able to export your own PostgreSQL data to a file on the file system when needed.