Postgres Export to CSV

In this tutorial, you will learn how to use Postgres Export to CSV. Basically, in this tutorial, you will gain the knowledge of exporting the PostgreSQL table data to the CSV file.

There are multiple ways to export the PostgreSQL data to a CSV file:

Postgres Export to CSV
using COPY command
using \COPY command
using pgAdmin

Table of Contents

Suppose you have a database with many tables, and you need to perform some analysis on the tables. Using PostgreSQL query, you can perform some complex analysis on the table like cleaning, transforming the data and etc.

But for complex statistical analysis, a PostgreSQL query is not enough. So for that, you can use other analytical tools for the analysis of the data, but how to get your data from the database to these analytical tools?

One way is to export the data from the database to a CSV file and then import the CSV file into these analytical tools. So PostgreSQL has two commands to export table data into CSV files, the ‘COPY’ and ‘\COPY’ commands.

“What is the difference between ‘COPY’ and ‘\COPY’ commands?”, both commands export the table data to a CSV file but ‘COPY’ is used on the server side, and ‘\COPY’ is used through psql for client side.

Let’s see some examples of how to export Postgresql table data to a CSV file using both commands.

Export to CSV in Postgres using COPY Command

The ‘COPY’ command is used on the server side for copying data from the database table to a CSV file and this is the easiest way to export the table data.

The syntax is given below.

COPY table_name|(sql query) TO 'path_with_csv_file_name' WITH DELIMITER ',' CSV HEADER;

COPY: It is the command to export the table to a CSV file.
table_name|(SQL query): Specify the table name from which you want to export the data to a CSV file, you can use the table name directly or use the SQL query like this (SELECT * FROM table_name).
TO ‘path_with_csv_file_name’: Specify the output file name with the path where you want to save the CSV file. It can be something like this ‘C:\Users\file_name.csv’.
WITH DELIMITER ‘,’: It is used to tell how to separate the columns and their value within a CSV file like using a comma or any other separator.
CSV HEADER: It is used to define that the CSV file will be saved with HEADER (this header is the column’s name of the table). If you don’t specify this option, then your CSV file will not contain any HEADER.

For example, you have a table called ‘customers’ with columns ‘first_name’, ‘last_name’, ‘country’, ‘account_status’, and ‘purchase_history’.

Export to CSV in Postgres using COPY Command Customers Table

Now, you want to export the above table data to a CSV file named ‘customers.csv’. So for that use the below command.

COPY customers 
TO 'C:\Users\Saurabh\Desktop\csv_data\customers.csv' WITH DELIMITER ',' CSV HEADER;

Go to the specified location for the CSV file.

Open the export CSV file ‘customers’ and you can see all the table data.

Export to CSV in Postgres using COPY Command

As you can the CSV file ‘customers’ opened in MS Excel and it represents the data in the tabular format. If you open the same file with Notepad, you will see the data something like this.

Export to CSV in Postgres using \COPY Command

The ‘\COPY’ command does the same task as the ‘COPY’ command but you can use this command through the PostgreSQL ‘psql’ prompt.

The syntax is given below.

\COPY table_name|(sql query) TO 'path_with_csv_file_name' WITH DELIMITER ',' CSV HEADER;

\COPY: It is the command to export the table to a CSV file.
table_name|(SQL query): Specify the table name from which you want to export the data to a CSV file, you can use the table name directly or use the SQL query like this (SELECT * FROM table_name).
TO ‘path_with_csv_file_name’: Specify the output file name with the path where you want to save the CSV file. It can be something like this ‘C:\Users\file_name.csv’.
WITH DELIMITER ‘,’: It is used to tell how to separate the columns and their value within a CSV file like using a comma or any other separator.
CSV HEADER: It is used to define that the CSV file will be saved with HEADER (this header is the column’s name of the table). If you don’t specify this option, then your CSV file will not contain any HEADER.

For example, you have another table ‘orders’ that stores the information on the order time and status and this table has columns ‘customer_id’, ‘order_time’, and ‘status’.

Export to CSV in Postgres using Slash COPY Command Orders Table

Open the command prompt or terminal on your computer and log in to the ‘psql’ prompt. If this command asks for a password then enter the password for the Postgresql user.

psql -U postgres -d sqlserverguides

Export the table ‘orders’ data to a CSV file named ‘orders.csv’ using the below command.

\COPY orders 
TO 'C:\Users\Saurabh\Desktop\csv_data\orders.csv' WITH DELIMITER ',' CSV HEADER;

Go to the location where the CSV file is exported.

Export to CSV in Postgres using Slash COPY Command Exported CSV File

Now open the CSV file using Notepad or any other text editor application.

Export to CSV in Postgres using Slash COPY Command Notepad CSV File

Export to CSV in Postgres using pdAdmin

The pgAdmin is the graphical user interface of PostgreSQL, using this you can perform all the tasks that you can do from the terminal or psql prompt. So here you will see how to export the database table to a CSV file.

Follow the below steps to export the table data to a CSV file on your local system.

Open the pdAdmin application and connect to the server that you have created for PostgreSQL.
Then locate the database where your table exists as shown in the below picture.

Export to CSV in Postgres using pdAdmin Server and Database Selection

Now select the table that you want to export as a CSV file like the ‘books’ table in the above output. Next, right-click on that table, and you see the context menu appears then select the options Import/Export.

Export to CSV in Postgres using pdAdmin Import and Export Options

An Import/Export dialog appears:

First, give the CSV file name ‘books.csv’ with the path where you want to save the file.
From the ‘Miscellaneous’ click on the button in front of the label ‘Header’ to ‘Yes’.
Then select the ‘Delimiter’ as a comma ‘,’ from the drop-down menu.
In last, click on the ‘Ok’ button to export the CSV file.

After performing the above steps, you have successfully exported the table ‘books’ data to the CSV file ‘books.csv’. Now go to the location where the CSV file is saved.

Export to CSV in Postgres using pdAdmin Exported CSV File

You can see the exported CSV file ‘books.csv’ in the above picture, after opening this file you see the data exported data from the table ‘books’ of the PostgreSQL database.

Conclusion

In this PostgreSQL tutorial, you have learned how to use the ‘COPY’ and ‘\COPY’ commands of PostgreSQL to copy or export the data from the table to the CSV file. Also covered how to export the database table data to a CSV file using the pdAdmin which is called the GUI of the PostgreSQL