News

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.

4 comments:

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?

-Rudi

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;