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 export PostgreSQL data into easy-to-use CSV files for analysis and data portability. Read on to learn the three methods.
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.
Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.
Start for free now!
Get Started with Hevo for Free
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 $
FOR tables IN
SELECT (schema || '.' || table_name) AS table_with_schema
statement := 'COPY ' || tables.table_with_schema || ' TO ''' || path || '/' || tables.table_with_schema || '.csv' ||''' DELIMITER '';'' CSV HEADER';
$ 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.
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.
Get a deeper dive into PostgreSQL and its capabilities with these essentials:
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.
visit our website to explore hevo
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.