Getting Started with psql

Getting familiar with psql is a very good productivity enhancer, so our advice is to spend some quality time with the documentation of the tool and get used to it. In this lesson, we’re going to simplify things and get started.

There are mainly two use cases for psql, either as an interactive tool or as a scripting and reporting tool.

Interactive tool

In the first case, the idea is that we have plenty of commands to help us get our work done, and we can type SQL right in our terminal and see the result of the query.

Scripting tool

In the scripting and reporting use case, we have advanced formatting commands: it’s possible to run a query and fetch its result directly in either asciidoc or HTML (for example, the given \pset format). Say we have a query that reports the N best-known results for a given driver surname. We can use psql to set dynamic variables, display tuples only, and format the result in a convenient HTML output:

psql postgres -h 0.0.0.0 \
--tuples-only \
--set n=1 \
--set name=Alesi \
--no-psqlrc \
-P format=html -d f1db \
-f /usercode/sql/report.sql

Connection parameters

It’s also possible to set the connection parameters as environment variables or to use the same connection strings as in our application’s code, so we can test them with copy/paste easily; there’s no need to transform them into the -d <dbname> -h <hostname> -p <port> -U <username> syntax:

~ psql -d postgresql://postgres@localhost:5432/f1db
f1db# 

~ psql -d "user=postgres host=localhost port=5432 dbname=f1db"
f1db#

Use case: Generating driver’s report

The query in the report.sql file uses the :'name' variable syntax. Please look at the following query.