PSQL_EDITOR: Fighting with Sublime Text under Windows

Table of Contents

This is a How-To blog about the PSQL_EDITOR variable. While trying to figure out all possible pitfalls, I've somehow managed to write a full blog post. You may skip to the summary though. All tests were done on Windows 10 with PostgreSQL 12 installed.

As you know, PostgreSQL ships with an interactive console tool named psql, which allows you to use a lot of enhanced features, like watching, for example. There is a lot of information about tweaking psql in POSIX environments. Still, there is a lack of such tips for Windows users. If you, my dear reader, are a Linux user, I'm sure you will also find this information useful anyway. Who knows what Microsoft's next purchase will be? After LinkedIn, GitHub, and Citus, you cannot be sure anymore! 😉

The Sublime text processor for the PSQL_EDITOR variable

I've chosen Sublime as a target because it's not a simple console or single window application, but rather an advanced text processor with enhanced file handling, e.g. it restores unsaved files on startup.

`PSQL_EDITOR` - an environment variable

According to the manual PSQL_EDITOR, EDITOR and VISUAL are environment variables, which are used inside psql to call an editor for input.

Let's try to set the environment variable then.

$ SET PSQL_EDITOR=C:Program FilesSublime Text 3subl.exe

$ psql

postgres=# e

postgres=# e
could not open temporary file 'C:Temppsql.edit.3288.sql': File exists

$ SET PSQL_EDITOR=C:Program FilesSublime Text 3subl.exe

$ psql

postgres=# e

could not open temporary file 'C:Temppsql.edit.3288.sql': File exists

As you can see, nothing was executed after the first e command, even though I've edited and saved the content of the query.
What's wrong here? First of all, I've used subl.exe instead of sublime.exe, which is special command line tool:

Sublime Text includes a command line tool, subl, to work with files on the command line. This can be used to open files and projects in Sublime Text, and also works as an EDITOR for unix tools, such as git and subversion.

psql runs the editor and then waits for the editor process to finish. But subl is a special tool, which in fact launches sublime.exe and then just dies. So psql gets a signal about subl, finishes and returns, but the temp file hasn't even been edited yet.

Next time you try to execute e, it reports on the temp file's existence.

Let's check the Sublime documentation:

Specifying -w will cause the subl command to not exit until the file is closed.

But the problem is: we cannot pass additional command line options in Windows:

$ SET PSQL_EDITOR=C:Program FilesSublime Text 3subl.exe -w

$ psql -d postgres 
psql (12.1) 
Type 'help' for help. 

postgres=# e     
''C:Program FilesSublime Text 3subl.exe -w'' is not recognized as 
an internal or external command, operable program or batch file.

$ SET PSQL_EDITOR=C:Program FilesSublime Text 3subl.exe -w

$ psql -d postgres

psql (12.1)

Type 'help' for help.

postgres=# e

''C:Program FilesSublime Text 3subl.exe -w'' is not recognized as

an internal or external command, operable program or batch file.

Let's try to examine the psql sources to find out if we can set PSQL_EDITOR to an arbitrary command line:

static bool
editFile(const char *fname, int lineno)
{
	...

		/*
	 * On Unix the EDITOR value should *not* be quoted, since it might include
	 * switches, eg, EDITOR='pico -t'; it's up to the user to put quotes in it
	 * if necessary.  But this policy is not very workable on Windows, due to
	 * severe brain damage in their command shell plus the fact that standard
	 * program paths include spaces.
	 */
	...
	if (lineno > 0)
		sys = psprintf(''%s' %s%d '%s'',
					   editorName, editor_lineno_arg, lineno, fname);
	else
		sys = psprintf(''%s' '%s'',
					   editorName, fname);
	...
}

static bool

editFile(const char *fname, int lineno)

{

...

* On Unix the EDITOR value should *not* be quoted, since it might include

* switches, eg, EDITOR='pico -t'; it's up to the user to put quotes in it

* if necessary. But this policy is not very workable on Windows, due to

* severe brain damage in their command shell plus the fact that standard

* program paths include spaces.

...

if (lineno > 0)

sys = psprintf(''%s' %s%d '%s'',

editorName, editor_lineno_arg, lineno, fname);

else

sys = psprintf(''%s' '%s'',

editorName, fname);

...

}

You can see now why we cannot include any arguments in the PSQL_EDITOR. It's quoted by default before execution on Windows.

The only additional environment variable available to configure editing is PSQL_EDITOR_LINENUMBER_ARG, but that won't help us here.

Of course, we may use just `sublime_text.exe`:

$ SET PSQL_EDITOR=C:Program FilesSublime Text 3sublime_text.exe

$ psql -d postgres  
psql (12.1)  
Type 'help' for help.

postgres=# set ECHO queries
postgres=# e         
select version();
                          version
------------------------------------------------------------
 PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit
(1 row)

$ SET PSQL_EDITOR=C:Program FilesSublime Text 3sublime_text.exe

$ psql -d postgres

psql (12.1)

Type 'help' for help.

postgres=# set ECHO queries

postgres=# e

select version();

version

------------------------------------------------------------

PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit

(1 row)

This approach has one definite disadvantage. You need to close the Sublime editor, not only the tab with the file if you want to continue to work with psql. Assuming we're usually working with a lot of files simultaneously, that is a huge problem.

To keep all the advantages of Sublime Text, you may create a simple batch script in the home folder:

$ ECHO @ECHO OFF > %USERPROFILE%subl.bat

$ ECHO 'C:Program FilesSublime Text 3subl.exe' --wait %* >> %USERPROFILE%subl.bat

$ type %USERPROFILE%subl.bat
@ECHO OFF
'C:Program FilesSublime Text 3subl.exe' --wait %*

$ SET PSQL_EDITOR=%USERPROFILE%subl.bat

$ psql -d postgres          
psql (12.1)                 
Type 'help' for help.       

postgres=# set ECHO queries
postgres=# e               
select version();
                          version
------------------------------------------------------------
 PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit
(1 row)

$ ECHO @ECHO OFF > %USERPROFILE%subl.bat

$ ECHO 'C:Program FilesSublime Text 3subl.exe' --wait %* >> %USERPROFILE%subl.bat

$ type %USERPROFILE%subl.bat

@ECHO OFF

'C:Program FilesSublime Text 3subl.exe' --wait %*

$ SET PSQL_EDITOR=%USERPROFILE%subl.bat

$ psql -d postgres

psql (12.1)

Type 'help' for help.

postgres=# set ECHO queries

postgres=# e

select version();

version

------------------------------------------------------------

PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit

(1 row)

It works like a charm! You've just closed the tab and continued working with psql normally.

The SET command vs. `setenv`

In the screenshot above, you may see a very common error people constantly make. They try to set EDITOR inside psql and use the SET command for that. SET is used for internal psql variables; to work with environment variables, you should use the setenv command. But this time, make sure you use proper quoting:

postgres=# setenv PSQL_EDITOR %USERPROFILE%subl.bat
invalid command subl.bat
Try ? for help.

postgres=# setenv PSQL_EDITOR '%USERPROFILE%subl.bat'
postgres=# e
select version();
                          version
------------------------------------------------------------
 PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit
(1 row)

postgres=# setenv PSQL_EDITOR %USERPROFILE%subl.bat

invalid command subl.bat

Try ? for help.

postgres=# setenv PSQL_EDITOR '%USERPROFILE%subl.bat'

postgres=# e

select version();

version

------------------------------------------------------------

PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit

(1 row)

TL;DR:

1. PSQL_EDITOR is the environment variable controlled by the shell. You should set it before calling psql or inside psql with the special command setenv
2. There are several synonyms available: PSQL_EDITOR, EDITOR, VISUAL
3. In Windows you cannot use an arbitrary shell command as a PSQL_EDITOR value, only the name or full path of the proper file (binary, batch, .cmd, etc), due to forced quoting within psql
4. If you need some complex command to be used, use a script for that (cmd, powershell) or create a shortcut with proper arguments.

To find out more about psql, see our other psql blogs here.