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
(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.


I speak at and organize conferences around Open Source in general and PostgreSQL in particular.


Jul 4, 2017
London, UK
Jul 5-7, 2017
St Petersburg, Russia
PGConf.EU 2017
Oct 24-27, 2017
Warsaw, Poland


PGCon 2017
May 23-26, 2017
Ottawa, Canada
Apr 26, 2017
Gothenburg, Sweden 2017
Mar 23, 2017
Paris, France
Nordic PGDay 2017
Mar 21, 2017
Stockholm, Sweden
Confoo Montreal 2017
Mar 8-10, 2017
Montreal, Canada
More past conferences