How to Export PostgreSQL Table to CSV

PostgreSQL is a popular database used by millions of websites & businesses around the world. One of the most common requirements while using PostgreSQL database is to be able to export PostgreSQL table to CSV file to be used in MS Excel and other tools for further analysis. In this article, we will look at how to export PostgreSQL table to CSV.

How to Export PostgreSQL Table to CSV

Here are the steps to export PostgreSQL table to CSV. There are two ways to export PostgreSQL data to CSV – using COPY command and using \copy command. We will look at both these use cases one by one.

Using COPY command

Here is the basic syntax of COPY command.

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

In the above command, replace table_name with your table name, /path/to/csv_file.csv with CSV file’s full path where you want to store the file.

The above command will export all rows & columns in your database. If you want to export only specific columns, then modify the above command as shown below.

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

In the above command you need to list the column names to be exported in a comma-separated manner, immediately after the table name.

The above command works in both Windows & Linux. Please make sure you use the right format for file path, depending on your operating system.

Using \copy command

If you want to export remote table to a local CSV file, use \copy command. Here is the syntax for it.

\copy (SELECT * FROM [table_name]) to [/path/to/csv_file.csv] CSV HEADER

The \copy command requires only elevated privileges, unlike COPY command that requires you to be logged in with root or sudo privileges.

Also \copy command allows you to provide relative file path to CSV file.

That’s it. In this article, we have learnt two different ways to export PostgreSQL tables to CSV files.

Also read:

How to Add Minutes to Datetime in Python
How to Disable Iptables in Ubuntu
How to Create Swap Space in CentOS, Redhat
How to Install RPM Package in Redhat, CentOS
How to Configure Firewalld in Redhat, CentOS