Commandline editing in psql on win32

Unix has readline. Or libedit. At least most of the time. Win32 has a much less capable commandline editor, but at least it has something. Unfortunatly, it's not something most people know about. There are a couple of nice features there that will help you out if you like using the commandline to work with your database. It does have the basic commandline editing and history functions, and also some (reasonably simple) macro handling.

First one of the main limitations - settings and data aren't persisted across sessions. Actually, they do survive a psql restart as long as it's restarted under the same cmd.exe, but if cmd is restarted, all is lost. This includes both history data, and macros. Very unfortunate, but you can learn to live with it...

Working with the commandline history is quite simple. The basic keys to use it are the usual: Up/Down arrow - move to previous/next command PgUp/PgDn - move to first/last command in current session left/right/home/end - move around on the current line ctrl-left/ctrl-right - move word left and right

There are also a couple of special keys to copy/paste parts of rows (same ones as on the commandprompt - which really goes for all these). Another very handy key is F7, which will bring up a list of all the commands in the history buffer in a popup window, where you can pick a command to paste in at the current line.

psql history screenshot

Finally, there is the macro functionality. Unfortunatly, your macros will be lost at restart of cmd, so if you just use the Start Menu icon for psql, you can't really use them. You can, however, create your own BAT file that will first register a bunch of macros, and then start psql. For the most basic form of macros, just run:

doskey /exename=psql.exe pgd=SELECT datname FROM pg_database;

To use this macro, simply type in the macro and hit enter. The macro has to be at the beginning of a line.

postgres=# pgd
datname
-----------
postgres
template1
template0
(3 rows)

You can also pass parameters to the macros, for example:

doskey /exename=psql.exe dbinfo=SELECT datname,pg_encoding_to_char(encoding) FROM pg_database WHERE datname='$1';

And then just pass a parameter on the commandline:

postgres=# dbinfo postgres
datname | pg_encoding_to_char
----------+---------------------
postgres | SQL_ASCII
(1 row)

Those of you who do a lot of hackery with the Windows commandprompt will recognise this as standard doskey macros and keys. Those who aren't might be interested in reference from Microsoft.