Export PostgreSQL Table Data To Excel CSV File From PSQL CLI

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

   
   

   1. -bash-3.1$ psql -U postgres database
   2. Welcome to psql 8.3.6, the PostgreSQL interactive terminal.
   3.  
   4. Type:  \copyright for distribution terms
   5. \h for help with SQL commands
   6. \? for help with psql commands
   7. \g or terminate with semicolon to execute query
   8. \q to quit
   9.  
   10. 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.
   bash

   
   

   1. 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.
   bash

   
   

   1. 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.
   bash

   
   

   1. haloror=# \\q
   2. -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

   
   

   1. -bash-3.1$ less database.csv
   2. 15 | 1234    |        |                   |                  1 |             1 |
   3. 43 | 5678    |        |                   |                  1 |             1 |
   4. 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.

See larger image PostgreSQL 8.4 Official Documentation – Volume II. Server Administration (Paperback) List Price: $36.99 USD New From: $36.99 USD In Stock Used from: $27.02 USD In Stock