Formatting Ad-Hoc Reports in PostgreSQL

OR/M and noSQL guys see SQL as an awkward way to store and retrieve objects from the database. Here at Vertabelo, we see SQL as a useful language to query data. Making reports in pure SQL is a pure pleasure. Running an interactive query is almost the same experience as asking a human to provide information.

At some point you’ll have a need to write a report in a few seconds. Running IDE and coding is not a viable option. In this article I’ll show you how to perform and format ad-hoc reports using a PostgreSQL command line client. I’ll focus on formatting options so examples will be simplified.

Here we go.

Case #1 – Single User Details

You have to retrieve information about one user and email it ASAP.

Here is a query:

select * from user_account wher id = 10101;

And the default output is:

Which is unreadable. Fortunately, there is an “\x” option which transposes output.

\x
select * from user_account wher id = 10101;

The output this time is far more readable than previously:

The “\x” option is really usefull if you have a lot of columns to show.

Case #2 – List of Users in a Readable Format

You have to make a list of your users and send it to the marketing department. Marketing guys are not used to geeky console outputs; they prefer graphical formats. HTML is a common format nowadays. PSQL can output rows as HTML. Here is a example script:

# switch to HTML output
\H 
# save output as report.htm
\o report.html
# add some HTML at the begining
\qecho 
# execute query
select * from user_account
# add some HTML at the end of the document
\qecho 
# quit
\q

I won’t try to describe all of PSQL’s options since the PostgreSQL documentation covers them very well. I just wanted to show you that quick & dirty solutions are good enough in some cases. PSQL has a lot of options. In addition to the options shown here, you can experiment with the “\f” option to generate a CSV file or the “\set” option to parametrize queries. Have fun!