How to Export a PostgreSQL Table to a CSV File

CSV (comma-separated values) format is a simple file format that stores tabular data such as database tables or spreadsheet data. Its simplicity makes CSV an ideal format for exporting and importing data between programs.

PostgreSQL, a powerful open-source relational database, provides multiple methods for exporting PostgreSQL data into CSV format. This guide will explore the three primary techniques for exporting PostgreSQL tables as CSV files: COPY, \copy, and pg_dump.

Understanding these three methods will equip you to efficiently perform Postgres export to CSV files for analysis and data portability. Read on to learn the three methods.

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.

Key Features of PostgreSQL

• Provides built-in support for JSON, XML, arrays, hstore, and custom types, making it ideal for modern applications that require complex data models.
• PostgreSQL is released under the PostgreSQL License (similar to MIT), which makes it free to use, modify, and distribute.
• Supports Atomicity, Consistency, Isolation, and Durability (ACID), ensuring reliable transactions and data integrity.

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 3 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 3: Postgres Export to CSV using the pg_dump

Method 1: Postgres Export to CSV using the COPY Command

The easiest but the most efficient way to export data from a Postgres table to a CSV file is by using the COPY command. 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 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

Method 3: Postgres Export to CSV using the pg_dump

pg_dump is the utility for backing up a PostgreSQL Database or tables. It can be used to extract data from the tables also. The syntax is as follows.

pg_dump --column-inserts --data-only --table=<table> <database> > table_name.sql

Here output file table_name.sql will be in the form of INSERT statements like:

INSERT INTO my_table (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

This output needs to be converted into a CSV file with the help of a small script in Bash or Python.

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.

Conclusion

As discussed, Postgres is a modern and open-source Database Management System allowing 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