In the previous tutorial, we showed you how toimport data from a CSV file into a table. We will use the samepersons table for importing data from a CSV file.

The following statement retrieves the data from thepersons table.

SELECT * FROM persons;

Output:

id | first_name | last_name |    dob     |              email----+------------+-----------+------------+---------------------------------  1 | John       | Doe       | 1995-01-05 | john.doe@example.com  2 | Jane       | Doe       | 1995-02-05 | jane.doe@example.com(2 rows)

Export data from a table to CSV using the COPY statement

TheCOPY statement allows you to export data from a table to a CSV file.

For example, if you want to export the data of thepersons table to a CSV file namedpersons_db.csv in theC:\temp folder, you can use the following statement:

COPY persons TO 'C:\temp\persons_db.csv' DELIMITER ',' CSV HEADER;

Output:

COPY 2

The output indicates that the command exported two rows.

In this example, the COPY statement exports all data from all columns of thepersons table to thepersons_db.csv file.

Sometimes, you may want to export data from some columns of a table to a CSV file. To achieve this, you can specify the column names together with the table name afterCOPY keyword.

For example, the following statement exports data from thefirst_name,last_name, andemail columns of thepersons table toperson_partial_db.csv

COPY persons(first_name,last_name,email)TO 'C:\temp\persons_partial_db.csv' DELIMITER ',' CSV HEADER;

If you don't want to export the header, which contains the column names of the table, you can remove theHEADER flag in theCOPY statement.

For example, the following statement exports only data from theemail column of thepersons table to a CSV file:

COPY persons(email)TO 'C:\temp\persons_email_db.csv' DELIMITER',' CSV;

Notice that the CSV file name that you specify in theCOPY command must be written directly by the server.

It means that the CSV file must reside on the database server machine, not your local machine. The CSV file also needs to be writable by the user that the PostgreSQL server runs as.

Export data from a table to a CSV file using the \copy command

If you have access to a remote PostgreSQL database server, but you don't have sufficient privileges to write to a file on it, you can use the PostgreSQL built-in command\copy.

The\copy command runs theCOPY statement behind the scenes. However, instead of the server writing the CSV file, psql writes the CSV file and transfers data from the server to your local file system.

To use\copy command, you need to have sufficient privileges to your local machine. It does not require PostgreSQL superuser privileges.

For example, if you want to export all data from thepersons table intopersons_client.csv file, you can execute the\copy command from the psql client as follows:

\copy (SELECT * FROM persons) to 'C:\temp\persons_client.csv' with csv

In this tutorial, we have shown you how to useCOPY statement and\copy command to export data from a table to CSV files.

Last updated onFebruary 1, 2024

On this page

• Export data from a table to CSV using the COPY statement
• Export data from a table to a CSV file using the copy command