How to Export a PostgreSQL Table to a CSV File

Exporting a PostgreSQL table to a CSV file is a common need, especially when sharing data, generating reports, or migrating to another system. I’ve been there, struggling with clunky manual exports, dealing with formatting issues, and spending way too much time trying to get the data just right. Whether you need a quick way to back up your data or move it to another platform like Excel or a data warehouse, I’ve got you covered.

In this guide, I’ll walk you through the two methods using COPY and \copy commands, so you can efficiently export PostgreSQL tables for analysis and data portability. Let’s dive in.

What is PostgreSQL?

PostgreSQL is an open-source, general-purpose, object-relational database management system, or ORDBMS. It is widely used and provides advanced features along with standard compliance. Some of these features include complex queries, foreign keys, triggers, and views—all supporting transactional integrity with full ACID compliance. Inherent in it is vast extensibility for custom data types, functions, and operators.

Postgres Export to CSV

CSV is a useful and universally accepted format for storing data. Many applications support importing and exporting data in CSV files because it allows data to be saved in an easy-to-read plain text or tabular format. However, many CSV files are generated for importing into other applications. You can export PostgreSQL data into CSV files and then import them into different programs or databases, depending on your use case.

You can Postgres Export to CSV in 2 ways, all slightly different.

• Method 1: Postgres Export to CSV using the COPY Command
• Method 2: Postgres Export to CSV using the \copy Command

Method 1: Postgres Export to CSV using the COPY Command

The easiest but most efficient way to export data from a Postgres table to a CSV file is by using the COPY command. The COPY command generates a CSV file on the Database Server. You can export the entire table or the results of a query to a CSV file with the COPY TO command.

COPY table or sql_query TO out_file_name WITH options

Let’s discuss a few examples to help you understand better.

• For example, let’s export the data of the employees table to a CSV file named employees_db.csv in the C:tmp folder. You can execute the following command in the psql prompt to copy the table to CSV.

COPY employees TO 'C:tmpemployees_db.csv'  WITH DELIMITER ',' CSV HEADER;

• In some cases, you may want to export data from just some columns of a table to a CSV file. Let’s export the data of contacts whose age is less than 45 years to a CSV file named young_contacts_db.csv in the C:tmp folder. You can execute the following command in the psql prompt.

COPY (select * from contacts where age < 45) TO 'C:tmpyoung_contacts_db.csv'  WITH DELIMITER ',' CSV HEADER;

Keep a few important things in mind while using the COPY command.

• The COPY command requires an absolute path to the file.
• TO specifies that the table or query is exported to a file.
• CSV specifies the type of file the data is being exported to.
• HEADER specifies that the first row of the .csv file is a header row and should be ignored while importing.
• DELIMITER specifies the character that separates columns within each line of the file. This, in our case, is a comma (,). 

Next, let’s take a look at how the COPY command can be used to extract data from multiple tables using a PL/PgSQL procedure. Here, the tables_to_extract table contains the details of the tables to be exported. You can execute the following command in the psql prompt.

CREATE OR REPLACE FUNCTION table_to_csv(path TEXT) RETURNS void AS $
 declare
    tables RECORD;
    statement TEXT;
 begin
 FOR tables IN 
    SELECT (schema || '.' || table_name) AS table_with_schema
    FROM tables_to_extract

LOOP
    statement := 'COPY ' || tables.table_with_schema || ' TO ''' || path || '/' || tables.table_with_schema || '.csv' ||''' DELIMITER '';'' CSV HEADER';
    EXECUTE statement;
 END LOOP;
 return;  
 end;
 $ LANGUAGE plpgsql;

SELECT db_to_csv('/home/user/dir'/dump); -- This will create one csv file per table, in /home/user/dir/dump/

Method 2: Postgres Export to CSV using the \copy Command

The \copy command is used to generate a CSV file for the client’s computer. It is useful for copying a database with restricted access and for creating a personal copy of the data. To use the /copy command, you need to have sufficient privileges to your local machine (client’s computer). It does not require you to have PostgreSQL superuser privileges.

\copy table or sql_query to out_file_name csv header

Let’s take a look at an example to help you understand better.

• For example, if you want to export all data of the employees table to a CSV file named employees_db.csv in the C:tmp folder. You can execute the following command in the psql prompt.

\copy (SELECT * FROM employees) to 'C:tmpemployees_db.csv' with csv

Additional Resources for PostgreSQL Integrations and Migrations

• How to load data from postgresql to biquery
• Migrate Data from Postgres to MySQL
• How to migrate Data from PostgreSQL to SQL Server
• PostgreSQL to Oracle Migration
• Replicate Postgres to Snowflake

Use Cases for Postgres to CSV Migration

• Archive old or inactive PostgreSQL data into CSV format for long-term storage or regulatory compliance.
• Export PostgreSQL data to CSV for use in reporting tools like Excel, Tableau, or Power BI for further analysis.
• Create CSV files as a lightweight backup of selected datasets, providing easy portability and access.
• Share PostgreSQL data with teams or third parties who do not have direct access to the database, enabling collaboration or data transfer.

What’s Next?

As discussed, Postgres is a modern and open-source database management system that allows you to work with relational databases. However, there is a high possibility that you might want to move your PostgreSQL data to different applications for further analysis and visualization. This is where Postgres Export to CSV comes in. This article took you through various techniques of exporting PostgreSQL data into CSV files.

You can now import the CSV files into different programs depending on your use case. But if you are looking for a reliable and error-free way of moving data from PostgreSQL to a destination of your choice, then Hevo is the right choice.

Get a deeper dive into PostgreSQL and its capabilities with these essentials:

• PostgreSQL SELECT Statement: Syntax & Example Queries | A 101 Guide
• PostgreSQL Analytics: A Comprehensive Guide 101
• PostgreSQL BETWEEN Operator: A Critical 101 Guide
• How to Sync Data from PostgreSQL to Google Bigquery

Sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs; check them out!

Frequently Asked Questions