How to Export Postgres to a CSV file
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
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
You’ll also need to make sure
psqlis installed. You can use the command
psql -Vto 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:
id SERIAL PRIMARY KEY,
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
We would get results that look like the following:
1 | Stephen | Emigh | 3777 E Richmond St #900 | firstname.lastname@example.org
2 | Tyra | Shields | 3 Fort Worth Ave | email@example.com
3 | Tammara | Wardrip | 4800 Black Horse Pike | firstname.lastname@example.org
4 | Cory | Gibes | 83649 W Belmont Ave | email@example.com
5 | Danica | Bruschke | 840 15th Ave | firstname.lastname@example.org
6 | Wilda | Giguere | 1747 Calle Amanecer #2 | email@example.com
7 | Elvera | Benimadho | 99385 Charity St #840 | firstname.lastname@example.org
8 | Carma | Vanheusen | 68556 Central Hwy | email@example.com
9 | Malinda | Hochard | 55 Riverside Ave | firstname.lastname@example.org
10 | Natalie | Fern | 7140 University Ave | email@example.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
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:
\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:
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