psql - save results of command to a file

Question

I'm using psql's \dt to list all tables in a database and I need to save the results.

What is the syntax to export the results of a psql command to a file?

Answer 1

From psql's help (\?):

\o [FILE] send all query results to file or |pipe

The sequence of commands will look like this:

[wist@scifres ~]$ psql db Welcome to psql 8.3.6, the PostgreSQL interactive terminal  db=>\o out.txt db=>\dt 

Then any db operation output will be written to out.txt. Enter '\o' to revert the output back to console.

db=>\o 

Answer 2

The psql \o command was already described by jhwist.

An alternative approach is using the COPY TO command to write directly to a file on the server. This has the advantage that it's dumped in an easy-to-parse format of your choice -- rather than psql's tabulated format. It's also very easy to import to another table/database using COPY FROM.

NB! This requires superuser privileges and will write to a file on the server.

Example: COPY (SELECT foo, bar FROM baz) TO '/tmp/query.csv' (format csv, delimiter ';')

Creates a CSV file with ';' as the field separator.

As always, see the documentation for details