psql - write a query and the query's output to a file

Question

In postgresql 9.3.1, when interactively developing a query using the psql command, the end result is sometimes to write the query results to a file: ``` boron.production=> \o /tmp/output boron.production=> select 1; boron.production=> \o boron.production=> \q $ cat /tmp/output ?column? ---------- 1 (1 row) ``` This works fine. But how can I get the query itself to be written to the file along with the query results? I've tried giving psql the `--echo-queries` switch: ``` -e, --echo-queries Copy all SQL commands sent to the server to standard output as well. This is equivalent to setting the variable ECHO to queries. ``` But this always echoes to stdout, not to the file I gave with the \o command. I've tried the `--echo-all` switch as well, but it does not appear to echo interactive input. Using command editing, I can repeat the query with `\qecho` in front of it. That works, but is tedious. Is there any way to direct an interactive psql session to write both the query and the query ou…

Accepted Answer

You can try redirecting the stdout to a file directly from your shell (Win or Linux should work)

```
psql -U postgres -c "select 1 as result" -e nomedb >> hello.txt
```

This has the drawback of not letting you see the output interactively. If that's a problem, you can either tail the output file in a separate terminal, or, if in *nix, use the `tee` utility:

```
psql -U postgres -c "select 1 as result" -e nomedb | tee hello.txt
```

Hope this helps!

Luca

psql - write a query and the query's output to a file

Solution

Problem