SPOOLing Queries with Results in psql
SQL*Plus, the Oracle database‘s command-line tool, provides the SPOOL command to “store query results in a file.” The next screen snapshot shows SPOOL used in SQL*Plus to spool the listing of user tables to a file called C:\pdf\output.txt
.
Both the executed query and the results of the query have been spooled to the file output.txt
as shown in the next listing of that file.
Oracle’s SQL*Plus’s SPOOL
-ed output.txt
SQL> select table_name from user_tables; TABLE_NAME ------------------------------ REGIONS LOCATIONS DEPARTMENTS JOBS EMPLOYEES JOB_HISTORY PEOPLE NUMERAL NUMBER_EXAMPLE COUNTRIES 10 rows selected. SQL> spool off
PostgreSQL‘s command-line tool, psql, provides functionality similar to SQL*Plus’s SPOOL
with the \o (\out) meta-command. The following screen snapshot shows this in action in psql
.
The file output.txt
written via psql
‘s \o
meta-command is shown in the next listing.
List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | albums | table | postgres (1 row)
Only the results of the query run in psql
are contained in the generated output.txt
file. The query itself, even the longer query produced by using \set ECHO_HIDDEN on
, is not contained in the output.
One approach to ensuring that the query itself is output with the query’s results written to the file is to use the \qecho
meta-command to explicitly write the query to the spooled file before running the query. This is demonstrated in the next screen snapshot.
Using \qecho
in conjunction with \o
does place the query itself in the written file with the query’s results as shown in the next listed output.
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)
The main downside to use of \qecho
is that it must be used before every statement to be written to the output file.
The psql
variable ECHO
can be set to queries
to have “all SQL commands sent to the server [sent] to standard output as well.” This is demonstrated in the next screen snapshot.
Unfortunately, although setting the psql
variable ECHO
to queries
leads to the query being output along with the results in the psql
window, the query is not written to the file by the \o
meta-command. Instead, when \o
is used with ECHO
set to queries
, the query itself is printed out again to the window and the results only are written to the specified file. This is because, as the documentation states (I added the emphasis), the \o
meta-command writes “the query output … to the standard output.” This is demonstrated in the next screen snapshot.
I have not been able to figure out a way to easily use the \o
meta-data command and have both the query and its results written to the file without needing to use \qecho
. However, another approach that doesn’t require \qecho
is to run not try to spool the file output from within psql
interactively, but to instead execute a SQL script input file externally.
For example, if I make an input file called input.sql
that consisted only of a single line with query
select * from albums;
I could run
psql
with the command
psql -U postgres --echo-queries < input.txt > outputWithQuery.txt
to read that single-line file with the query and write output to the outputWithQuery.txt
file. The --echo-queries
option works like the \set ECHO queries
from within psql
and running this command successfully generates the prescribed output file with query and results. The following screen snapshot and the code listing following that demonstrate this.
outputWithQuery.txt
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)
I don’t know how to exactly imitate SQL*Plus’s writing of the query with its results from within SQL*Plus in psql
without needing to add \qecho
meta-commands, but passing the input script to psql
with the --echo-queries
option works very similarly to invoking and spooling the script from within SQL*Plus.
Reference: | SPOOLing Queries with Results in psql from our JCG partner Dustin Marx at the Inspired by Actual Events blog. |
Hi Dustin,
Thank you very much for you research and sharing the knowledge. regarding the last part how do we combine both “I don’t know how to exactly imitate SQL*Plus’s writing of the query with its results from within SQL*Plus in psql without needing to add \qecho meta-commands” We have a way of achieving this . Once you logged into database using PSQL all we need to do is pass the script as input file.
\i C:/Users/nkanchet/temp/input.sql;