Thursday, May 03, 2007

Export data from Postgres

To export data from Postgres to an output file of your choice can be done by following the simple steps below:

1. Start psql with the database that you'd like to export from...

$ psql [DATABASE]

2. Toggle the output mode to unaligned (\a toggles between unaligned and aligned output mode)

=# \a

3. Turn "tuples only" off (\t toggles between tuples on and off)

=# \t

4. Set the output file (replace [FILE] with what you'd like to call your output file). It will send all query results to the file or |pipe.

=# \o [FILE]

5. Run whatever query you'd like to send to the output file. For example,


In summary:

\o /tmp/outputfile.txt
SELECT ......


eggyknap said...

Other options include pg_dump ( and COPY ... TO ( If you happen to be using R for statistics/analysis/regression/machine learning, you can also use RdbiPgSQL to get data into R directly. PostgreSQL has drivers for ODBC and JDBC, so getting data directly into other software packages should also work.

Matt Smith said...

Additionally, here is a link to some tips:

pg_dump and pg_dumpall