Welcome to End Point’s blog

Ongoing observations by End Point people

Using psql \o to append to a file

I had a slow query I was working on recently, and wanted to capture the output of EXPLAIN ANALYZE to a file. This is easy, with psql's \o command:

5432 josh@josh# \o explain-results

Once EXPLAIN ANALYZE had finished running, I wanted the psql output back in my psql console window. This, too, is easy, using the \o command without a filename:

5432 josh@josh# \o

But later, after adding an index or two and changing some settings, I wanted to run a new EXPLAIN ANALYZE, and I wanted its output appended to the explain-analyze file I built earlier. At least on my system, \o will normally overwrite the target file, which would mean I'd lose my original results. I realize it's simple to, say, pipe output to a new file ("explain-analyze-2"), but I wasn't interested. Instead, because \o can also accept a pipe character and a shell command to pipe its output to, I did this:

5432 josh@josh# \o | cat - >> explain-results

Life is good.

Update: A helpful commenter pointed out I hadn't actually used the same files in the original post. Oops. Fixed.


Anonymous said...

... but life may be better if you had used the same file names.

Jon Jensen said...

Josh, FWIW, the - is optional to cat; stdin is the default if no args are passed.

Another similar option if you want to see the output too, is to pipe to tee -a.

Rudi Shumpert said...

Nice Post.

I've been trying to get an export of a query to a csv via the command line. I've tried the copy command, but I am having trouble. Have you ever exported the whole result set, with column headers exported out to a csv?


Joshua Tolley said...

Rudi --

This works for me, on the PostgreSQL 8.3.9 system I happened to have available.

copy (select * from pg_database) to '/home/josh/test.csv' with csv header;