Originally from: tweet, LinkedIn post.

psql shortcuts

I post a new PostgreSQL "howto" article every day. Join me in this journey – subscribe, provide feedback, share!

In my tool postgres_dba, the setup instructions have this:

printf "%s %s %s %s\n" \\set dba \'\\\\i $(pwd)/start.psql\' >> ~/.psqlrc

This provides a way to call start.psql by just typing :dba in psql, and this line works in both bash and zsh (which is good because macOS switched to zsh as the default shell a few years ago, while bash is usually the default shell on Linux distros).

One can easily add their own instructions to .psqlrc, defining various handy shortcuts. For example:

\set pid 'select pg_backend_pid() as pid;'
\set prim 'select not pg_is_in_recovery() as is_primary;'
\set a 'select state, count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1 order by 1;'

👉 This adds simple shortcuts:

Current PID:

nik=# :pid
  pid
--------
 513553
(1 row)

Is this not a primary?

nik=# :prim
 is_primary
------------
 t
(1 row)

Simple activity summary

nik=# :a
        state        | count
---------------------+-------
 active              |    19
 idle                |   193
 idle in transaction |     2
                     |     7
(4 rows)

The value of a currently set psql variable can be passed in an SQL context as a string using this interesting syntax:

nik=# select :'pid';
            ?column?
---------------------------------
 select pg_backend_pid() as pid;
(1 row)

It is important not to forget to use the option -X ( --no-psqlrc) in scripting, so nothing that you put in .psqlrc would affect the logic of your scripts.