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.


Conferences

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

Upcoming

Postgres Vision 2016
Oct 11-Nov 13, 2016
San Francisco, USA
PGConf.EU 2016
Nov 1-Jan 4, 2016
Tallinn, Estonia
PGConf.Asia 2016
Dec 2-3, 2016
Tokyo, Japan

Past

Postgres Open
Sep 13-16, 2016
Dallas, USA
Stockholm PUG 2016/4
Aug 31, 2016
Stockholm, Sweden
PG Day'16 Russia
Jul 6-8, 2016
St Petersburg, Russia
PGDay UK 2016
Jul 05, 2016
London, UK
Stockholm PUG 2016/3
Jun 16, 2016
Stockholm, Sweden
More past conferences