escape code

One of the nicer feautures of PostgresQL is its client: psql. This nifty console application has builtin readline support. As I am spending so much time in psql sessions, it is worth to learn how to abuse the readline library for some key board magic.

Readline is what you are actually working with (or against!) in an interactive Bash session. ZSH has its own infrastructure for interaction, but there are quite some applications besides Bash which make use of this interaction infrastructure. A basic list of readline keyboard shortcuts has been compiled at this page, and most of these will be in the muscle memory of console warriors. Suffice to say that this is just a part of the default configuration, see man 3 readline for some more possibilities.

If you find that you are typing often the same thing in a readline supported application, you might be interested to hear that Readline kindly offers to do the typing for you. These are called keyboard macros, and are configured in readline's configuration file: ~/.inputrc.

The following is very useful, almost taken directly from man 3 readline. If you put this in ~/.inputrc, pressing Alt-q will put single quotes around the word currently under the cursor:

"^[q": "^[b\'^[f\'"

This looks cryptic, so let's have a closer look. Before : is the key combination that triggers the action after the colon. ^[q translates into Alt-q, so that will trigger the action.

But how do you know that ^[q happens to represent Alt-q? The good news is that you don't, when you use the following method in vim to generate the escape code. In insert mode, press CTRL-V. This will tell vim to put literally what comes next. If you press Alt-q directly after CTRL-v, this sequence will show up, and that is all there is to it. You will notice, that when moving the cursor over the ^[, the cursor will jump over the two signs as if it is one. That is because it _is_ one character. Copy pasting these escape sequences into your editor most likely will not work.

So what about the action after the colon? This is a sequence that you can type literally in psql. Press Alt-b (bound to backward-word), then type a single quote (which in ~/.inputrc needs to be escaped by a backslash). Continue by pressing Alt+f, which brings you to the end of the word, where another single quote is inserted. Result: The current word is quoted. With this configuration in place, you just need to press one key combination, instead of needing complicated eye-hand coordination to move cursor to where it should be.

As I am working with a horribly normalized datamodel where finding what one needs can take an icredible amount of time, I often query the system tables from postgres to navigate in the database. A question that I keep repeating to ask, is: In which tables does this column name appear? A valuable indicator for the role of the table is the amount of information it contains. The following query helps me a lot to quickly find what I am actually looking for:

select distinct
    relname as table,
    reltuples as rowcount
from information_schema.columns as cols
join pg_class on pg_class.relname = cols.table_name
where column_name = 'name-of-column'
    and reltuples > 0
order by reltuples;

It is a bit of a nuisance to need to edit this text often to enter the column that I am looking for. With the following line, I can type the column on the command line, and after pressing Alt-c (mnemonic: columns), the following happens:

=> colname<Alt-c>
=> select distinct
->   relname as table,
->   reltuples as rowcount
-> from information_schema.columns as cols
-> join pg_class on pg_class.relname = cols.table_name
-> where column_name = 'colname'
->   and reltuples > 0
-> order by reltuples;
┌─────────────┬──────────┐
│    table    │ rowcount │
├─────────────┼──────────┤
│ table1      │        7 │
│ table2      │       10 │
│ table3      │       22 │
│ table4      │       80 │
│ table5      │      126 │
│ table6      │    13460 │
│ table7      │    50112 │
└─────────────┴──────────┘
(7 rows)

The following, admittedly very ugly, line in ~/.,inputc makes this possible:

"^[c": "\C-a\C-kselect distinct\n  relname as table,\n  reltuples as rowcount \nfrom information_schema.columns as cols \njoin pg_class on pg_class.relname = cols.table_name \nwhere column_name = '\C-y'\n  and reltuples > 0 \norder by reltuples;\n"

CTRL+a:

The cursor is moved to the start of the line.

CTRL+k:

Everything starting from the cursor until the end of the line is killed, or deleted and stored in the kill buffer.

Then, a lot of typing happens, including newlines, and some spacing. Until just after the first quote.

CTRL+y:

The contents of the kill buffer is yanked in place

...and some conluding typing happens.

This keyboard shortcut does not make a lot of sense in any other program than psql. Luckily, you can select the program for which this shortcut is available by encapsulating the configuration as follows:

$if psql
    # some configuration
$endif

This way, you can reprogram all your keys, including function keys!, to do something useful.