Postgres Export to CSV

CSV, or Comma Separated Values, is a widely used file format to store the tabular data. It provides a simple and lightweight way to represent the data where each line in the file corresponds to a row, and commas separate the values within each row.

In addition, CSV files are highly compatible with a comprehensive array of programming languages which make them an ideal choice for data interchange between different systems.
In this tutorial, we will explore the various methods and techniques that we can use to export the data from PostgreSQL into a CSV format.

Requirements:

This tutorial uses the Pagila sample database which is provided on the official PostgreSQL page. However, the methods work on any PostgreSQL database.

We also assume that you have access to the PSQL utility or pgAdmin 4 and above to interact with your PostgreSQL cluster.

PostgreSQL Export to CSV: Copy Command

The simplest method to export a database table into the CSV format is using the “copy” command in PostgreSQL.

Start by connecting to your target database using your preferred method. In this tutorial, we use the pgAdmin tool.

Once connected to the database, select the table from which you wish to export the data. If you are on PSQL tool, you can use the “\dt” command to show all the tables in the current database.

Locate the table that you want to export and note down its name.

We can export the data from a PostgreSQL table to a CSV file using the “copy” command. This command allows us to copy the data between a table and a file in various formats including CSV.

To export the table into a CSV file, we can use the syntax as shown in the following:

Replace the table_name and the file_path parameters with your target table and path to the CSV file.

Add the HEADER option at the end if you wish to include the column headers in the exported file. PostgreSQL exports the data without the column names by default if you omit this option.

Consider the following example that uses the “copy” command to export the data from the film table in the Pagila database to a CSV file called “fim.csv” in the current working directory:

NOTE: In some instances, the “copy” command fails to export the data when using a relative path. Ensure to use the absolute paths to stay on the safe side.

Once you run the “copy” command, you should see a message which indicates the number of the copied rows.

Example Output:

NOTE: With the PSQL utility, use the “\copy” command instead of the “copy” command. This performs the action on client-side instead of the server-side.

PostgreSQL Export to CSV: PgAdmin 4

We can export a PostgreSQL database table to CSV using the pgAdmin tool if you prefer a graphical interface.

You can follow the following outlined steps to accomplish this:

Launch PgAdmin and connect to your PostgreSQL database.

Navigate to the table that you want to export in the object explorer.

Right-click on the table and select “Import/Export”.

Choose the “Export” tab in the “Import/Export” dialog, and specify the output file path and filename in the “Filename” field.

Select the “CSV” format option.

Optionally, check the “Include column names in the first row” box to include the headers.

Click the “OK” or “Export” button to start the export process.

Once completed, you should see the process start and process completion status messages from the right-bottom corner.

Conclusion

We explored how we can use the copy, \copy, and the pgAdmin utility to export a given database table to a CSV file in PostgreSQL.