PostgreSQL - Export PostgreSQL Table to CSV file

In this article we will discuss the process of exporting a PostgreSQL Table to a CSV file. Here we will see how to export on the server and also on the client machine.

For Server-Side Export:

Use the below syntax to copy a PostgreSQL table from the server itself:

Syntax: COPY Table_Name TO 'Path/filename.csv' CSV HEADER;

Note: If you have permission to perform a read/write operation on the server-side then use this command.

Example:

First, let’s create a table with columns id, first_name, last_name, and email to the database:

CREATE TABLE students(
   id SERIAL PRIMARY KEY,
   first_name VARCHAR,
   last_name VARCHAR,
   email VARCHAR UNIQUE
);

Let’s insert some data into our students table:

INSERT INTO students(first_name, last_name, email)
VALUES('Virender', 'Sehwag', '[email protected]'),
    ('Hardik', 'Pandiya', '[email protected]'),
    ('Shreyas', 'Iyer', '[email protected]'),
    ('Rishabh', 'Pant', '[email protected]');

Now check the data in the table:

SELECT * FROM students;

Output:

Now export the above table as a CSV file.

COPY students TO '/tmp/student1.csv' CSV HEADER;

Note: Make sure that the path that you specify should have read/write permission.

If everything works fine then it should look like this:

The CSV file would look like below:

We can also specify columns that we want to export or write a query for the data.

COPY (SELECT first_name FROM students) TO '/tmp/student.csv' CSV HEADER;

Output:

The CSV file would look like below:

For Client-Side Export:

Use the below syntax for client-side export of CSV file:

Syntax: \copy Table_Name to 'Path/filename.csv' CSV HEADER

If you do not have permission to perform a read/write operation on the server-side and want to copy the table to the client-side machine then use this command.

Let's use the students table here also.

Execute the below command to export the table to a CSV file.

\copy students to '/tmp/students.csv' CSV HEADER

Output:

The CSV file would look like below:

You can give a query to select data here also.