16

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 output to a file?

Improve this question
1
  • 1
    \o outputs query result to the output file... I'm not aware of the way how to do it. You can just put text in the output using \qecho... or you can run the SQL in batch - using redirections. Then the -e will work. Commented Dec 9, 2013 at 8:23

3 Answers 3

Reset to default
21

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Image
I just realized that, with the addition of tee, this answers my question pretty well, so I took the liberty of editing the answer to show how to use tee; and with that, awarding the long overdue checkmark. Thank you for your answer!
6

I know this is an old question, but at least in 9.3 and current versions this is possible using Query Buffer meta-commands shown in the documentation or \? from the psql console: https://www.postgresql.org/docs/9.3/static/app-psql.html

\w or \write filename
\w or \write |command

Outputs the current query buffer to the file filename or pipes it to the shell command command.
Improve this answer

1 Comment

Image
Thank you for helping out with this old question. The \w (aka \write) command writes previous commands, without their output (tested in psql 9.4.8). What is wanted instead is for future commands with their output to be written. Still, given that we currently have to use other means to accomplish my goal, this is a good command to know.
1

Please try this format as I got the output from the same:

psql -h $host -p $port -q -U $user -d $Dbname -c "SELECT \"Employee-id\",\"Employee-name\" FROM Employee_table" >> Employee_Date.csv

I need the output in a CSV file.

Improve this answer

1 Comment

Image
add --csv to your psql arguments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.