psql - save results of command to a file

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

\copy which is a postgres command can work for any user. Don't know if it works for \dt or not, but general syntax is reproduced from the following link Postgres SQL copy syntax

\copy (select * from tempTable limit 100) to 'filenameinquotes' with header delimiter as ','

The above will save the output of the select query in the filename provided as a csv file

EDIT:

For my psql server the following command works this is an older version v8.5

copy (select * from table1) to 'full_path_filename' csv header;

Use o parameter of pgsql command.

-o, --output=FILENAME send query results to file (or |pipe)

psql -d DatabaseName -U UserName -c "SELECT * FROM TABLE" -o /root/Desktop/file.txt

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