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 isactive
.You’ll also need to make sure
psql
is installed. You can use the commandpsql -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:
1 | 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:
1 | 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:
1 2 3 4 5 6 7 | 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:
1 | 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:
1 | 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:
1 | SELECT * FROM student; |
We would get results that look like the following:
1 2 3 4 5 6 7 8 9 10 11 12 | 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
:
1 | \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:
1 | \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:
1 | \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:
1 | cat student.csv |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started