Postgres Export to CSV

Recently, I was required to export data to CSV format in PostgreSQL. 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.

Table of Contents

Suppose you have a database with multiple tables, and you need to perform an analysis on these tables. Using a PostgreSQL query, you can perform some complex analysis on the table, such as cleaning, transforming the data, etc.

However, for complex statistical analysis, a PostgreSQL query is insufficient. Therefore, you can utilize other analytical tools for data analysis, but how do you extract your data from the database to these 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.

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

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

Approach-1: 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: This command exports 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 as follows: 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 a header, which is the column names 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’.

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

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’ to view all the table data.

how to export csv file into postgresql database

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

Approach-2: 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 a header (this header is the column names 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 information on order time and status, with columns ‘customer_id’, ‘order_time’, and ‘status’.

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 ‘orders’ table data to a CSV file named ‘orders.csv’ using the command below.

\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.

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

Approach-3: Using pdAdmin

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. Here, you will learn how to export a database table to a CSV file.

Follow the steps below 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 picture below.

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

how to export data from postgresql to csv

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’ section, click on the button in front of the label ‘Header’ to ‘Yes’.
Then select the ‘Delimiter’ as a comma from the drop-down menu.
Lastly, click on the ‘Ok’ button to export the CSV file.

how to export table to csv in postgresql

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.

You can see the exported CSV file, ‘books.csv’, in the picture above. After opening this file, you see the data exported 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 a 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