I have to admit that I’m more and more enamored by PostgreSQL. Since I often use its default client psql, I started searching the Internet for some features I’d like it to have – and I wasn’t disappointed.
First of all, I am accustomed to what Bash does with my history: not putting commands beginning with a space into it, and saving identical subsequent history entries only once. It turns out that psql can do exactly the same – the manual even says:
Note: This feature was shamelessly plagiarized from Bash.
It turns out that it is enough to add the line
\set HISTCONTROL ignoreboth
to your ~/.psqlrc file (creating it if necessary).
This, however, is only a beginning. Here are a few things I find especially useful with psql.
First of all, there is the “expanded” mode, usually turned on by the \x metacommand. (“Metacommands” are commands preceded with a backslash and interpreted by psql and not the PostgreSQL server.) In expanded mode, instead of writing out each row in a single line of text, every column’s value is written out in its own line. This is very useful for queries returning a lot of things, but much less useful for queries returning only a handful of columns. If you encounter both cases frequently, consider using \x auto, which automatically enables the expanded mode if the data returned by the query would be wider than the screen.
Another nice command is \C (that is, backslash followed by capital “C”). It sets the title of every table printed as a result of a query. This doesn’t seem very useful unless you decide to use the control character sequence clearing the terminal as the title. This way, every query will be presented on its own, starting with an empty screen. Of course, you do not need to remember that sequence – psql’s metacommands accept command-line commands in backticks, so you can say \C `clear` and you’re good to go.
There is also a lot of commands which operate on the “current query buffer”. This expression means roughly “whatever precedes the metacommand”. (If there is nothing preceding it, it usually operates on the previous query instead.) For instance, \e opens your favorite text editor with the current query buffer or the previous query and lets you edit it in a nicer way than psql’s readline library. Another useful one is \gx, which turns the expanded mode on for just this query (or repeats the last query with expanded mode temporarily on). There is also \w, which writes the current query buffer (or the last query) into a file, and so on.
There are also some commands which act as wrappers around SQL’s filesystem-manipulating features, like the COPY TO and a few others. The problem with COPY TO is that the file it generates is owned by the postgres user, which is usually not what we want. Therefore, psql includes a wrapper around COPY, called \copy, which reads or writes files as the user running psql. This may be quite handy both in scripts (metacommands can be used in psql scripts, did you know that? There is even a full set of conditionals!).
The last thing I wanted to mention is the \watch metacommand. It is very similar to the watch command-line utility, which runs a given command periodically and updates the screen with its standard output. The psql \watch has a few drawbacks – the biggest one being that it does not clear the screen before each execution of the given query. (This can be remedied with the \C `clear` trick, though.) Another potential issue is that it cannot do stuff watch can like highlighting differences. Still, it is much more comfortable than running psql with watch (since then quoting quickly becomes nightmarish).