When working on a project where I was required to merge two sets of like data to make sure there were not any conflicts I needed a way to dump PostgreSQL database tables to Excel formatted .CSV files. Anyhow dumping Postgres tables to excel is easy to do by following the below directions.

Export A PostgreSQL Table To A CSV File Using PSQL CLI:

1. Launch PSQL: Issue the command below to launch the Postgres CLI but make sure to change the user and table to the correct parameters for your configuration. You can probably use postgres as the username but make sure to change database to the proper name of your database.

   -bash-3.1$ psql -U postgres database
   Welcome to psql 8.3.6, the PostgreSQL interactive terminal.
   
   Type:  copyright for distribution terms
   h for help with SQL commands
   ? for help with psql commands
   g or terminate with semicolon to execute query
   q to quit
   
   database=#
   

2. Set Output File: Issue the below command to specify the name and location of the file that Postgres will output your data to. In the below example its a good idea to name the file relating to the table you will be dumping the data from. You should also make sure that the postgres user has access to write data to the directory where you are specifying the file.

   database=# \o /var/lib/pgsql/table.csv
   

3. Output Postgres Table Data: Now issue the below command to output the data into the specified file.You can specify the entire table or you could also specify only certain columns that you want to output to the CSV file. The below example shows exporting an entire table by the name of table.

   database=# select * from table;
   

4. Exit Postgres: Now issue “\q” from the PSQL CLI to quit Postgres as shown in the below syntax. As you can see below the command will send you back to the shell prompt.

   haloror=# \q
   -bash-3.1$
   

5. Verify CSV Format: Now look at the CSV file you created by using the less command as shown below. You can see the file contains only data separated properly so Excel can read the data.

   -bash-3.1$ less database.csv
   15 | 1234    |        |                   |                  1 |             1 |
   43 | 5678    |        |                   |                  1 |             1 |
   44 | 6482    |        |                   |                  1 |             1 |
   

You can now easily export Postgres data into Excel files as long as you have CLI access to your Postgres database.

PostgreSQL 8.4 Official Documentation – Volume II. Server Administration

The PostgreSQL Global Development Group. Fultus Corporation 2009, Paperback, 436 pages, $33.29

PostgreSQL (Developer’s Library)

Korry Douglas. Addison-Wesley Professional 2010, Paperback, 800 pages, $54.99

This entry was posted on Friday, January 8th, 2010 at 8:17 AM and is filed under Insights. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.