How to Export PostgreSQL Table to CSV File
If you are a developer, then that you might have come across this issue. Here you can see, how to export PostgreSQL Table to a CSV file. PostgreSQL is a major database, which was used by most of the developers in major applications.
This database was providing robust functions with more features than any other database which was available in the market. Since PostgreSQL database was open source, it was used in enterprise level database management system and it also has more features that makes it as a storage for the major players around the globe.
Lets see how to export PostgreSQL table to CSV file in different methods.
We have more number of options to export PostgreSQL database table to a CSV file.
While I was doing programming to develop one of my application, I faced this situation to export SQL database table to CSV file. So I used the Internet to search for more examples, and did research on each and every one of the example code, and finally found out the successful commands which was used to export PostgreSQL database table to a CSV file.
As the first step, If you are using the psql command, then you need to use the following command to export the table to a CSV file.
\COPY (SELECT * FROM <TABLE_NAME>) TO 'D:\CSV-EXPORT_FILE.csv' WITH CSV DELIMITER ',';
You need to use the “\” slash if you are running this command in the psql. If not this command will show you errors.
The CSV file generated will be in different format according to the database values which you have given.
In this code you need to mention the location to save the CSV file and you also need to correctly mention the table name which you need to export to CSV file.
Another format of exporting PostgreSQL table to a CSV file is through a Windows batch script. Here I am only going to show you how to export PostgreSQL table to CSV file in Windows because, I have only searched and did all my research only for Windows.
So for windows, you need to create a batch file with the following code and save it in a specific location.
@echo off setlocal set PGPASSWORD=<Enter the postgres password> "C:\Program Files\PostgreSQL\9.4\bin\psql.exe" -h localhost -U postgres -d <database_name> -f D:\sql-scripts\cmd.sql
after that you also need to create a SQL file with the following hole and save it in the location where you have saved the Windows batch file the code which was created in the SQL was nothing but the table which you need to export to CSV file I have simply used as select query to export data from the SQL database table
COPY (SELECT * FROM <TABLE_NAME>) TO 'D:\CSV-EXPORT_FILE.csv'
After saving these two files make sure it was saved in the same location and run the Windows batch file so that it will export the CSV file to the location which you have specified in the SQL file.
Make use of this command and export the PostgreSQL table to CSV file. This command is 100% working.