How to set xterm titles in psql

As most of you might know, you can customize the prompts in psql, the PostgreSQL interactive terminal. If you didn't know, you can read up on it in the documentation. You can also add color to your prompt using VT100 control sequences; an example of that can be found in said documentation. I have my shell and psql prompts in different colors so I can tell quickly when I'm logged in to what. It spares me the embarrassment of typing "ls" into psql.

As it turns out, you can use that same mechanism to customize the xterm titles from within psql. It might not be completely clear why that is necessary. After all, if you have customized the xterm titles in your shell, say to show the name of the command currently running, then it would already show something like "psql somedb" when you are using psql. The trick, or perhaps danger, with that is that if you use commands like \c or SET SESSION AUTHORIZATION in psql, then your connection parameters change, but the shell doesn't update the xterm title. This had me confused more than once now.

The control sequence to change the xterm title is ESC]0;stringBEL, where ESC is \033 and BEL is \007. In psql prompt language, this becomes [%033]0;string%007%]. Add that somewhere to your prompt (doesn't really matter where, as it won't be printed). Say you are using the default prompt (boring!) and want to show "psql dbname" in the xterm window title when logged in to PostgreSQL, then put this into your ~/.psqlrc:

\set PROMPT1 '%[%033]0;psql %/%007%]%/%R%# '

Note: If you don't have your shell to set up to update the xterm title, then the title set by psql will remain after you quit psql. That might be even more confusing, so don't try this if you don't have your shell set up for this.

If you have other fun and/or useful ideas for the psql prompt, please share them in the comments.