Postgres on the command line with external editor not running query

Troubleshooting External Editor Queries in PostgreSQL Command Line

Learn why your external editor might not be executing queries directly from the PostgreSQL command line and how to fix it, focusing on common pitfalls and configuration.

Working with PostgreSQL from the command line (psql) is powerful, but when you integrate an external editor like Sublime Text 2 for writing complex queries, you might encounter a common frustration: the query isn't executed automatically upon saving and exiting the editor. This article delves into the reasons behind this behavior and provides clear solutions to ensure your workflow is smooth and efficient.

Understanding psql's External Editor Mechanism

The psql client uses the environment variables EDITOR or VISUAL to determine which external editor to launch when you type \e (or \e filename.sql). When you invoke \e, psql creates a temporary file, opens it in your specified editor, and waits for the editor process to terminate. Once you save and close the file in your editor, psql reads the content of that temporary file and executes it as a SQL query. The key here is that psql expects the editor to exit for it to process the file. If your editor doesn't exit, psql remains in a waiting state.

sequenceDiagram
    participant User
    participant psql
    participant ExternalEditor

    User->>psql: Type `\e`
    psql->>psql: Create temporary SQL file
    psql->>ExternalEditor: Launch editor with temp file (waits)
    ExternalEditor->>User: Display temp file for editing
    User->>ExternalEditor: Edit and save query
    User->>ExternalEditor: Close editor
    ExternalEditor-->>psql: Editor process terminates
    psql->>psql: Read temp file content
    psql->>psql: Execute SQL query
    psql-->>User: Display query results

Sequence diagram of psql's external editor workflow.

Common Causes for Non-Execution

The primary reason your query might not execute is that your external editor, particularly GUI-based ones like Sublime Text, might not be configured to exit the process when you close the window. Instead, it might just close the window while the application process continues running in the background. This leaves psql indefinitely waiting for the editor process to terminate.

Solutions for Sublime Text 2 (and similar GUI editors)

To make Sublime Text 2 (or other GUI editors) work correctly with psql's \e command, you need a wrapper script that launches the editor in a way that allows psql to continue once the editing session is complete. This usually involves launching the editor in the background and then immediately exiting the wrapper script, or using a command that specifically waits for the editor to close the file.

1. Create a Wrapper Script

Create a shell script, for example, named subl-wait.sh, in a directory included in your system's PATH (e.g., /usr/local/bin or ~/bin).

2. Add Script Content

Populate the script with content that launches Sublime Text and waits for it to close the file. For Sublime Text, the --wait flag is crucial.

3. Make Script Executable

Ensure the script has execute permissions.

4. Set Environment Variable

Configure your EDITOR or VISUAL environment variable to point to this new wrapper script. This can be done in your shell's configuration file (e.g., ~/.bashrc, ~/.zshrc).

5. Reload Shell Configuration

Apply the changes by sourcing your shell configuration file or opening a new terminal session.

#!/bin/bash

# Path to your Sublime Text executable
SUBL_PATH="/Applications/Sublime Text 2.app/Contents/SharedSupport/bin/subl"

# Check if Sublime Text exists
if [ ! -f "$SUBL_PATH" ]; then
    echo "Error: Sublime Text 2 executable not found at $SUBL_PATH"
    exit 1
fi

# Launch Sublime Text with the --wait flag
# This makes the 'subl' command wait until the edited file is closed.
"$SUBL_PATH" --wait "$@"

Example subl-wait.sh wrapper script for Sublime Text 2.

chmod +x /usr/local/bin/subl-wait.sh

Making the wrapper script executable.

export EDITOR="/usr/local/bin/subl-wait.sh"
# Or for VISUAL
# export VISUAL="/usr/local/bin/subl-wait.sh"

Setting the EDITOR environment variable.

After following these steps, when you type \e in psql, Sublime Text will open the temporary file. Once you save your changes and close the Sublime Text window (or the specific file tab if Sublime Text is configured to close tabs rather than the whole application), the subl-wait.sh script will terminate, signaling psql to read and execute the query.