Handy psql TricksPublished
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 user on the
What else can we put in our
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
\pset null '[null]'
Are you interested in seeing the timing for every query that you run? Try adding this:
Does having your results piped to a pager like
less annoy or confuse you? You can disable the pager by adding this to your
\pset pager off
Do you like having your SQL keywords (e.g.
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
\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
\set ON_ERROR_ROLLBACK interactive
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:
To disable the easier to read format just run
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!