Skip to main content

psql tuning

.psqlrc​

The file ~/.psqlrc can be used to set some settings by default. For example:

echo '\timing on' >> ~/.psqlrc

Now if we start psql:

❯ psql -U postgres
Timing is on.
psql (15.5 (Debian 15.5-1.pgdg120+1))
Type "help" for help.

nik=# select pg_sleep(.5);
 pg_sleep
----------

(1 row)

Time: 508.187 ms

❗Important: for scripting involving psql, it is a good idea to use the option -X – to ignore ~/.psqlrc settings, so the logic (e.g., psql output analysis) won't depend on ~/.psqlrc at all.

pspg​

pspg is awesome. If you can, install it.

For example, for the query select * from pg_class limit 3;, before installation:

psql ugly output

After installation:

pspg improved output

pspg menus

To install:

  • on macOS/Homebrew: brew install pspg
  • Ubuntu/Debian: sudo apt update && sudo apt install -y pspg

Then:

echo "\setenv PAGER pspg
\pset border 2
\pset linestyle unicode
\set x '\setenv PAGER less'
\set xx '\setenv PAGER \'pspg -bX --no-mouse\''
" >> ~/.psqlrc

NULLs​

By default, NULLs are invisible in psql output:

nik=# select null;
 ?column?
----------

(1 row)

To fix it (put it to ~/.psqrc for persistency):

\pset null 'Ø'

Now:

nik=# select null;
 ?column?
----------
 Ø
(1 row)

postgres_dba​

postgres_dba is my collection of scripts for psql, with menu support:

postgres_dba menu support