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,

=# SELECT * FROM [TABLE];


In summary:

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

2 comments:

eggyknap said...

Other options include pg_dump (http://www.postgresql.org/docs/8.3/interactive/app-pgdump.html) and COPY ... TO (http://www.postgresql.org/docs/8.3/interactive/sql-copy.html). 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:
http://www.debian-administration.org/articles/218

pg_dump and pg_dumpall