Skip to main content

psql shortcuts

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:

1) Current PID:

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

2) Is this not a primary?

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

3) Simple activity summary

```sql
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.