When you use the psql command to work with your PostgreSQL database server it starts out pretty dumb and it tells you very little about what is going on:

paul@lappy:~$ psql -U postgres
psql (14.1 (Debian 14.1-1.pgdg110+1), server 13.5 (Debian 13.5-1.pgdg110+1))
Type "help" for help.

postgres=#

The only thing that the above prompt tells you is that you are connected to the postgres database and that you are a superuser. Or does it tell you that you’re connected as the postgres user? (It is the former.) And you’re supposed to know that # means “superuser”. What if we could make this prompt more useful? Let’s create a file in our home directory called .psqlrc and update the prompt.

\set PROMPT1 '[%m] %n@%/%R%#%x '
\set PROMPT2 '[%m] %n@%/%R %# '

Now when we log in we see a much more informative prompt:

paul@lappy:~$ psql -U postgres
psql (14.1 (Debian 14.1-1.pgdg110+1), server 13.5 (Debian 13.5-1.pgdg110+1))
Type "help" for help.

[[local]] postgres@postgres=#

We now know that we are connected to the local database server as the postgres user connected to the postgres database. I personally think that it is pretty clear that postgres@postgres means postgres user on the postgres database.

What else can we put in our .psqlrc file?

When you’re running queries against the database wouldn’t it be handy to know if a column contains a NULL value or if it just contains blank data? You can make that happen by adding this which will replace NULL values with [null]:

\pset null '[null]'

Are you interested in seeing the timing for every query that you run? Try adding this:

\timing on

Does having your results piped to a pager like less annoy or confuse you? You can disable the pager by adding this to your .psqlrc file.

\pset pager off

Do you like having your SQL keywords (e.g. SELECT, INSERT, etc.) automatically completed to upper case (or maybe lower case)? You can add this to your .psqlrc file to have tab completion automatically convert your SQL keywords to upper case. (Or lower-case if you swap upper for lower.)

\set COMP_KEYWORD_CASE upper

Next, maybe you want to do some transactional stuff with psql. You start by writing BEGIN; to start your transaction before you run a really long query that takes hours to finish. After it finishes you want to check that it did what you expected before you commit, so you try to run a SELECT query, but you make a typo and now your really long query is lost because the transaction is now busted. You can avoid this by wrapping each query in its own sub-transaction so that your BEGIN statement takes effect and creates a larger transaction but then each statement is its own transaction that rolls back automatically on an error while keeping your larger transaction intact. You can enable this feature by adding this to your .psqlrc file.

\set ON_ERROR_ROLLBACK interactive

By using interactive instead of on then this feature will only be enabled in interactive psql sessions and not all the time. That means that psql -f myscript.sql will still error out if there is a typo which is probably what you want and expect.

Finally, maybe you are looking at one or two rows of a really wide table and you’re finding it hard to read all of the columns. You can always just run this to turn on a pretty format:

\x

To disable the easier to read format just run \x again.

Pretty tricky, right? The PostgreSQL wiki has some links to other sites that talk about other tricks. The above are just the ones that I found most useful. Enjoy!