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', 'virender.sehwag@gfg.com'), ('Hardik', 'Pandiya', 'hardik.Pandiya@gfg.com'), ('Shreyas', 'Iyer', 'shreyas.iyer@gfg.com'), ('Rishabh', 'Pant', 'rishabh.pant@gfg.com');
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:
CSV File
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.
Contact Us