More on Spooling Queries and Results in psql

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.