Writing a conditional psql script

psql supports the conditional meta-commands \if, \elif, \else, and \endif. In this recipe, we will demonstrate some of them.

Getting ready

We want to improve the vartest.sql, script so that it runs VACUUM only if there actually are dead rows in that table.

How to do it…

We add conditional commands to vartest.sql resulting in the following script:

SELECT schemaname
, relname
, n_dead_tup
, n_live_tup
, n_dead_tup > 0 AS needs_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 1
\gset
\if :needs_vacuum
\qecho Running VACUUM on table :"relname...