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:
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.
Additionally, here is a link to some tips:
http://www.debian-administration.org/articles/218
pg_dump and pg_dumpall
Post a Comment