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