While working with databases like PostgreSQL, exporting a table into a CSV file or importing a CSV file into a Postgres table is a very common task. In PostgreSQL, you have multiple ways to export a table into a CSV file, such as copy statement, \copy command, or pgAdmin.
In this write-up, we will learn different methods to export a Postgres table into a CSV file. So, let’s begin.
How Does the COPY Statement Work in PostgreSQL?
Copy statement is the simplest way to export a table into a CSV file. To achieve this purpose, use the following syntax:
COPY tab_name TO 'Path/file_name.csv' CSV HEADER;
- COPY is a statement.
- tab_name is a table to be exported.
- Path represents a directory where the table will be exported.
- File_name.csv is the exported CSV file.
Example #1:
We have created a table named book_info. Let's list down the table details using the SELECT command:
SELECT * FROM book_info;
Now run the following command to export the book_info table into a CSV file:
COPY book_info TO 'C:\Windows\Temp\book_info.csv' CSV HEADER;
The output shows that five records have been exported. If everything goes fine, you will get the following results:
The output shows that the desired table has been exported to the CSV file successfully.
Example #2:
You can specify a delimiter of your choice to separate the table’s values. For instance, the below statement will provide the “;” separated values:
COPY book_info TO 'C:\Windows\Temp\book_info1.csv' DELIMITER ';' CSV HEADER;
Open the CSV file from the targeted location to see the exported values:
The output shows that the desired table has been exported to the CSV file. And this time, values are separated with “;”.
Example #3:
Execute the below statement to export only specific columns of a Postgres table:
COPY book_info(book_name) TO 'C:\Windows\Temp\book_info.csv' CSV HEADER;
Let’s open the CSV file to see the exported values:
In this way, you can export only specific columns of a table.
How Does the \COPY Command Work in PostgreSQL?
The \COPY is an inbuilt command that is used for exporting a PostgreSQL table to a CSV file. If you have limited privileges, then you can use the \COPY command because it doesn’t require superuser privileges. Follow the below-given syntax for the client-side export of CSV files:
\COPY Tab_Name to 'Path/file_name.csv' CSV HEADER;
Example
For exporting the desired table to a CSV file, execute the \COPY command:
\COPY (SELECT * FROM book_info) to 'C:\Windows\Temp\bookInfo.csv' with CSV;
On successful execution of the \COPY command, following data will be exported to the CSV file:
This is how you can export a Postgres Table to a CSV file using the COPY statement or \COPY command.
Conclusion
In PostgreSQL, there are multiple ways to export a table into a CSV file, such as the COPY statement or \COPY command. Using these commands, you can export the entire Postgres table or some specific columns of a table into a CSV file. This post explained the working of the COPY statement and \COPY command with examples.
