
Easier working with PostgreSQL – part I. pager for psql

This month we are starting a new series of articles on interesting and not always known tools that can significantly facilitate work with databases based on the PostgreSQL engine. In this article, we’ll look at the pager dedicated to use with psql. We will also show why working with a less database may not be […]
This month we are starting a new series of articles on interesting and not always known tools that can significantly facilitate work with databases based on the PostgreSQL engine. In this article, we’ll look at the pager dedicated to use with psql. We will also show why working with a less database may not be the optimal solution.
Because I myself am a significant supporter of using the text environment, I would also like to show thanks to this series that it is not as scary as it is painted and convince you to try the unconvinced text environment. On the other hand, I would like to show people interesting, maybe new, solutions for known problems.
Standard pagers available in Linux systems – below more
and less
.
pspg – pager especially for tabular data
Due to the specificity of the data returned by queries, the use of ordinary programs for wrapping text, such as less
or more
leaves much to be desired. Of course, there are possibilities to configure them to work with tables returned by queries (about configuration psql
we wrote in third part of the guide „psql Tips & Tricks ”), but sometimes it is quite bothersome.
We can use the program pspg
which allows you to operate with data returned by the database. Below we present data from the same query as in the above gif.
As you can see, the results look much better. An additional advantage is the ability to choose your own color scheme that will look best. The following screenshot shows all the color options that you can use.
Installation
pspg
is available from the official repository maintained by PostgreSQL Global Development Group. Therefore, after adding a repository, an Enterprise-class Linux installation requires only:
sudo yum install -y pspg
Setting pspg as the default pager for PostgreSQL
To set pspg
as the default pagination program, just add to the file .bashrc
variable PSQL_PAGER
. Thanks to this, we will change it only when using psql
to connect to the database. This can be achieved, e.g. by calling:
echo 'PSQL_PAGER = "pspg" '>> ~/.bashrc
For Friendly Interactive Shell users (we wrote about fish previously in the blog) to the configuration file just add:
set -Ux PSQL_PAGER pspg
Pspg options
using pspg
we can expect the same opportunities that we know from the program less
. We can still search for text by pressing the key /
. In case of pspg
all matches will be highlighted.
An interesting addition that has been implemented in pspg
, is possibility of freezing freeze) selected column from the result of the query, which if you want to pull a large number of columns out of the table, definitely makes it easier to read the returned results.
Another nice convenience is the possibility of another sorting data without having to call the query again and use the clause ORDERED BY
. Below is a screenshot of all available commands.
Finally, it is worth mentioning that this application uses the principle that its capabilities are easily possible to discover during normal work due to the interactive nature of the operation.
Summary
I hope to include pspg
to work with PostgreSQL will give you as much pleasure as me. Thank you very much for the time spent reading this article. I also encourage you to subscribe to our newsletter. Thanks to this, each of you will be sure that similar articles will go straight to your mailboxes and will not be accidentally overlooked ;-)