PostgreSQL allows us to export the contents of a table to a CSV file. The abbreviation CSV stands for comma-separated values. PostgreSQL has many commands for exporting data to CSV. We may export both the table’s content and the query’s results as CSV files which is extremely useful for transferring data from one database application to another or transferring tables across computer systems.
How to Export CSV from PostgreSQL?
- The PostgreSQL table or query result set can be exported as a CSV file using the \copy command.
- The file extension for the \copy command should be CSV.
- We can also export Postgres Tables to a CSV file using pgAdmin.
Exporting CSV File Using psql Command Line
To effectively perform the PostgreSQL export operation using the COPY command, you must have PostgreSQL superuser access.
Below are the steps you need to follow and after that, you will be able to export any PostgreSQL table to a CSV file.
Step 1: Open psql Command Line
You can open the psql command line from the start bar and you will be able to connect to PostgreSQL after entering the correct password.
Step 2: Select a Table
Consider the table named students from where we want to export all the data.
SELECT * FROM students;Code language: SQL (Structured Query Language) (sql)
Step 3: Query for Exporting PostgreSQL Table to CSV File
Copy the full table content of the students table using the below query:
\COPY students TO 'C:/temp/students_table.csv' WITH CSV HEADER;Code language: JavaScript (javascript)
Step 4: Verify the CSV File
After executing the above query, verify the table present in the temp folder in the C drive.
So this is how you export Postgres Table to a CSV file using the command line.
Exporting CSV Files Using pgAdmin
pgAdmin is an open-source tool for managing PostgreSQL databases. Exporting a CSV file with pgAdmin is a self-explanatory process.
Following these steps will allow you to export data to a CSV file.
Step 1: Launch pgAdmin and Set Up the Connection
Step 2: Select the Database You Want to Work On
In this tutorial, we used the Postgres database, you may use an existing database from which you want to export the table. The query for creating a new database will be:
CREATE DATABASE databasename;Code language: SQL (Structured Query Language) (sql)Step 3: Show Table
Execute the following query to see the data from the selected table:
SELECT * FROM sale;Code language: SQL (Structured Query Language) (sql)
Step 4: Export the Table
Right-click over the table, and select Import/Export Data
Step 5: Exporting the Table Using Export Wizard
In the “Import/Export Data Window“, pick the “Export” option, enter the “file name“, and specify the file format.
Enable the “header” option, set the delimiter, and click the “OK” button to export the data from the Postgres database to a CSV file
After clicking the OK button, you will see a pop-up window showing the process is completed.
Finally, you will see the Status as Finished, which indicates that your data was properly exported.
Step 6: Verify Table Data
Let’s see if the data from the specified table was exported to a CSV file or not. To accomplish this, browse to the directory/location where you exported the chosen table.
A CSV file called “sale.csv” has been exported to the provided location. Now, open it to examine its contents.
The above table indicates that the content of the “sale” table was successfully exported to a CSV file named “sale.csv”.
Summary
Using pgAdmin and psql command line, we can export CSV files from PostgreSQL tables. This tutorial walks you through the steps of exporting Postgres tables to CSV files using pgAdmin and psql command line. You can also read about Exporting MySQL Table to a CSV File, if you are interested in MySQL. We hope you enjoyed it
Reference
https://stackoverflow.com/questions/44022977/postgresql-export-data-to-csv-file