How to Export a PostgreSQL Table to a CSV File

PostgreSQL is an open-source Relational Database taking the world by storm, both on the ground and up there in the Cloud. It is one of the most advanced Relational Databases out there offering standard SQL features along with some modern ones like triggers, transaction integrity, etc. PostgreSQL has been active in development for about 30 years now and is used by companies and individual Developers all over the world. This article will provide you with detailed information on Postgres Export to CSV.

Comma-Separated Values (CSV) file is a delimited text file that contains a list of data separated by commas. CSV is a standard and universally accepted file data collection format. Many applications output their data in CSV form, which means you can easily use a CSV file to exchange data between different Applications, Programs, or Databases. This article takes you through various steps required to export data from PostgreSQL into a CSV file. But before getting started with Postgres Export to CSV, let’s discuss this robust Relational Database in brief.

What is PostgreSQL?

PostgreSQL, also known as Postgres, is a free and open-source Database that allows us to work with Relational Databases. It is a row-oriented database and is best suited for transactional workloads. As the name suggests, Postgres emphasizes SQL compliance and is used as the primary Data Store or Data Warehouse by many Web, Mobile, and Analytics Applications. PostgreSQL is completely free for everyone to use and requires little to no maintenance, making it very popular among users all over the world.

PostgreSQL offers all the standard and modern features expected by an Enterprise Transactional Database like unique primary keys, foreign key referential integrity, user-defined types, multi-version concurrency control, etc. It also allows you to store 100s of TBs of data in tables. PostgreSQL Relational Database System is a powerful and scalable platform based on a Single Server Architecture with no concept of Clusters. It supports various types of advanced data types such as UUID, JSON, Network Addresses, Geometric Data, etc.

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

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 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/

Postgres Export to CSV using the copy Command

The copy command is used to generate a CSV file to 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 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

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.

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 then import the CSV files into different programs depending on your use case. But if you are looking for a much reliable and error-free way of moving data from PostgreSQL to a destination of your choice, then Hevo is the right choice.

visit our website to explore hevo

Hevo Data with its strong integration with 100+ Sources & BI tools, such as PostgreSQL, allows you to not only export data from sources & load data in the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools.

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!

Share your experience of working with Postgres Export to CSV in the comments section below.