How to Export a PostgreSQL Table to CSV

Introduction

PostgreSQL (or Postgres) is an object-relational database management system similar to MySQL but supports enhanced functionality and stability. One excellent feature is that you can export a Postgres table to a .CSV file. This can be especially helpful when transferring a table to a different system or importing it to another database application.

In this tutorial, you will learn how to export a Postgres table to a .CSV file using the \copy and COPY commands.

Prerequisites

• PostgreSQL installed on the system
• An existing database in PostgreSQL
• A terminal window / command line (Ctrl+Alt+T)

Before You Begin with PostgreSQL

Postgres can be installed on Windows, Mac, Linux, and it can even be deployed inside a Docker container. This guide walks you through the procedure using Ubuntu Linux. However, all export commands can be used in any other supported operating system.

If you don’t have Postgres, you can install it by downloading the software from the developer’s website. Install Postgres on Ubuntu from the default repositories by entering the following:

sudo apt-get install postgresql

Once the service starts, you need to create or import a database.

Export Data from Table to .CSV with COPY Command

In psql there are two different commands.

The basic usage of the COPY command is as follows:

COPY db_name TO [/path/to/destination/db_name.csv] DELIMITER ‘,’ CSV HEADER;

Replace db_name with the actual name of your database and the /path/to/destination with the actual location you want to store the .CSV file in.

For example, in Windows, you might want to save a copy to C:\tmp\db_name.csv. In Linux, the default path could be /tmp/db_name.csv. Then, you can open the .CSV file and see the content of the table listed in a slightly different format. You can see the difference in the example below.

First, we list the content of a specified table in the psql shell with the command:

SELECT * FROM [table_name]

The output displays the values inside our example table as in the image below:

Now you can compare it to its corresponding .CSV file. The data exported from the previously mentioned table appears in a .CSV file, as seen in the following image:

Export Specific Columns from Table to .CSV

You can use the COPY command to target specific columns:

COPY [db_name](column1,column2,column3) TO [/path/to/destination/db_name_partial.csv] DELIMITER ‘,’ CSV HEADER;

Export Data from Table to .CSV with \copy Command

Use the \copy command when working remotely. It allows you to export data from a server to a .CSV file on a local client machine. Use it as follows:

\copy (SELECT * FROM [db_name]) to [/path/to/destination] CSV HEADER

Replace db_name with the name of the source database. Replace /path/to/destination with the actual location for the .CSV file. In Windows, you might export to C:tmpdb_name.csv. In Linux, you might export to /tmp/db_name.csv.

The \copy command only requires elevated privileges for the client system. This is helpful if you don’t have sufficient privileges to run the COPY command on the server. Also, the \copy command allows you to use a relative path.

For example, you could specify desktop/db_name.csv on a Windows system, and it would save to the current user’s desktop.

Conclusion

You should now be able to export PostgreSQL tables to .CSV using the copy or COPY commands. You can now import the .CSV to another computer system or database-management tool.