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.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

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:

sudo su - postgres

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:

psql some_username -h 127.0.0.1 -d some_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:

CREATE TABLE student(
id SERIAL PRIMARY KEY,
firstname CHAR(50),
lastname CHAR(50),
address VARCHAR(255),
email VARCHAR(50)
);

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:

INSERT INTO TABLE_NAME(COLUMN1, COLUMN2, COLUMN3) VALUES (VALUES1, VALUES2, VALUES3);

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

INSERT INTO student(firstname, lastname, address, email) VALUES('Stephen', 'Emigh', '3777 E Richmond St #900', 'stephen_emigh@hotmail.com');

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:

SELECT * FROM student;

We would get results that look like the following:

id | firstname | lastname | address | email
----+----------------------------------------------------+----------------------------------------------------+-------------------------+---------------------------
1 | Stephen | Emigh | 3777 E Richmond St #900 | stephen_emigh@hotmail.com
2 | Tyra | Shields | 3 Fort Worth Ave | tshields@gmail.com
3 | Tammara | Wardrip | 4800 Black Horse Pike | twardrip@cox.net
4 | Cory | Gibes | 83649 W Belmont Ave | cory.gibes@gmail.com
5 | Danica | Bruschke | 840 15th Ave | danica_bruschke@gmail.com
6 | Wilda | Giguere | 1747 Calle Amanecer #2 | wilda@cox.net
7 | Elvera | Benimadho | 99385 Charity St #840 | elvera.benimadho@cox.net
8 | Carma | Vanheusen | 68556 Central Hwy | carma@cox.net
9 | Malinda | Hochard | 55 Riverside Ave | malinda.hochard@yahoo.com
10 | Natalie | Fern | 7140 University Ave | natalie.fern@hotmail.com

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:

\COPY student TO 'student.csv' DELIMITER ',' CSV HEADER;

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:

\COPY student TO 'student.csv' DELIMITER ',' CSV HEADER;

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:

\COPY some_table TO 'some-directory/my-postgres-data.csv' DELIMITER ',' CSV HEADER;

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:

cat student.csv

Screenshot of the COPY statement in psql exporting a PostgreSQL table as a CSV file

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

Screenshot of a CSV file that was created from a PostgreSQL table in psql

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.