Blog

In this article we will introduce an interesting alternative for psqlwhich can significantly improve the work with the database. Pgcli is a database terminal interface focused on facilitating interaction with the text environment and with greater capabilities than a standard tool.

At the beginning it is worth asking yourself – „what I miss in psql”? In my opinion, this is a very complete tool that allows free configuration. However, I imagine situations where the interface provided by the creators of the PostgreSQL engine as standard is not enough. One example is the interactive creation of a query where possibilities pgcli may outshine psql.

According to the preferences of the creator of this project, which we can read about on the FAQ page, it is: (…) a big fan of CLI. In particular, REPL. (…). And actually pgcli is a tool that tries to maximally simplify the work in the – read-eval-print loop, i.e. load-evaluate-print.

Installation

The installation process when using PostgreSQL Global Development Group repositories is very simple. In case of Enterprise Linux family, the installation boils down to the following command:

yum install -y pgcli

Now you can connect, for example, to a locally operating base, for example by using the simplest authorization mechanism as in the example below:

sudo -u postgres pgcli -U postgres

possibilities pgcli

As you can read in the description itself, pgcli is a PostgreSQL interface with self-complete and syntax coloring. In addition, when editing, we can use two text input styles known from the Bash – shell as in Emacs or according to the editing method in Vi. In addition, particularly repetitive queries can be saved in the form of so-called named queries named queries). In the case of administrative work, this convenience can be an interesting alternative to calling queries saved in files.

Autocomplete

pgcliis distinguished by the use of so-called intelligent complementation. This means that the list of hints does not consist only of a list of keywords that match the text you are typing, and it is narrowed only to those hints that match the context. For example, after entering the beginning of the query:

SELECT * FROM

the supplement may conclude that only the names of tables or other objects behaving like them can be used here. In practice, it looks like this:

Appearance and configuration

As in the case of psqlwe can adjust the incentive ( prompt) commands to your own preferences according to the configuration file (which you can read about in prompt documentation). Many of the known of psql commands are also available in this application. The full list can be displayed by calling \?.

It is worth noting that using the command \pager, we can set any application wrapping text, including, presented in the previous article in this series, pspg.

We make all changes to the configuration in the file ~/.config/pgcli/config. Getting to know its content is the easiest way to learn about the possibilities pgcli. Here you can also define a color scheme according to your preferences. In addition, you can find options corresponding to, among others for the format of displayed numbers, NULL values, size of characters of completed keywords, etc.

Summary

As you can see, the multitude of available tools allows you to revive the usually black and white console environment. Additional opportunities can significantly facilitate and even make work more enjoyable. Finally, thank you for your time and encourage you to subscribe to our newsletter.