In the recent blog post SPOOLing Queries with Results in psql, I looked briefly at some PostgreSQL database psql meta-commands and options that can be used to emulate Oracle database‘s SQL*Plus spooling behavior. In that post, I wrote, “I have not been able to figure out a way to … have both the query and its results written to the file without needing to use \qecho.” Fortunately, since that writing, a colleague pointed me to the psql option --log-file (or -L).
The PostgreSQL psql documentation states that the --log-file / -L option “write[s] all query output into file filename, in addition to the normal output destination.” This handy single option prints both the query and its non-error results to the indicated file. For example, if I start psql with the command “psql -U postgres -L C:\output\albums.txt” and then run the query select * from albums;, the generated file C:\output\albums.txt appears like this:
********* QUERY **********
select * from albums;
**************************
title | artist | year
---------------------------+-----------------+------
Back in Black | AC/DC | 1980
Slippery When Wet | Bon Jovi | 1986
Third Stage | Boston | 1986
Hysteria | Def Leppard | 1987
Some Great Reward | Depeche Mode | 1984
Violator | Depeche Mode | 1990
Brothers in Arms | Dire Straits | 1985
Rio | Duran Duran | 1982
Hotel California | Eagles | 1976
Rumours | Fleetwood Mac | 1977
Kick | INXS | 1987
Appetite for Destruction | Guns N' Roses | 1987
Thriller | Michael Jackson | 1982
Welcome to the Real World | Mr. Mister | 1985
Never Mind | Nirvana | 1991
Please | Pet Shop Boys | 1986
The Dark Side of the Moon | Pink Floyd | 1973
Look Sharp! | Roxette | 1988
Songs from the Big Chair | Tears for Fears | 1985
Synchronicity | The Police | 1983
Into the Gap | Thompson Twins | 1984
The Joshua Tree | U2 | 1987
1984 | Van Halen | 1984
(23 rows)One drawback when using -L is that any error messages are not written to the file that the queries and successful results are written to. The next screen snapshot demonstrates an error caused by querying from the column name rather than from the table name and the listing after the screen snapshot shows what appears in the output file.
********* QUERY ********** select * from artist; **************************
The output file generated with psql‘s -L option shows the incorrect query, but the generated file does not include the error message that was shown in the psql terminal application (‘ERROR: relation “artist” does not exist’). I don’t know of any way to easily ensure that this error message is written to the same file that the query is written to. Redirection of standard output and standard error is a possibility, but then I’d need to redirect the error messages to a different file than the file to which the query and output are being written based on the filename provided with the -L option.
| Reference: | More on Spooling Queries and Results in psql from our JCG partner Dustin Marx at the Inspired by Actual Events blog. |
