Thread: psql command line editing

My web searches suggest that using the psql command line I'm limited to
moving the cursor one character at a time. Is there a way to use a small
editor, e.g., joe on linux, to move by words or to the begining and end of
the line?

TIA,

Rich

On 2/12/25 10:50, Rich Shepard wrote:
> My web searches suggest that using the psql command line I'm limited to
> moving the cursor one character at a time. Is there a way to use a small
> editor, e.g., joe on linux, to move by words or to the begining and end of
> the line?

From:

https://www.postgresql.org/docs/current/app-psql.html

"\e or \edit [ filename ] [ line_number ]

     If filename is specified, the file is edited; after the editor 
exits, the file's content is copied into the current query buffer. If no 
filename is given, the current query buffer is copied to a temporary 
file which is then edited in the same fashion. Or, if the current query 
buffer is empty, the most recently executed query is copied to a 
temporary file and edited in the same fashion.

     If you edit a file or the previous query, and you quit the editor 
without modifying the file, the query buffer is cleared. Otherwise, the 
new contents of the query buffer are re-parsed according to the normal 
rules of psql, treating the whole buffer as a single line. Any complete 
queries are immediately executed; that is, if the query buffer contains 
or ends with a semicolon, everything up to that point is executed and 
removed from the query buffer. Whatever remains in the query buffer is 
redisplayed. Type semicolon or \g to send it, or \r to cancel it by 
clearing the query buffer.

     Treating the buffer as a single line primarily affects 
meta-commands: whatever is in the buffer after a meta-command will be 
taken as argument(s) to the meta-command, even if it spans multiple 
lines. (Thus you cannot make meta-command-using scripts this way. Use \i 
for that.)

     If a line number is specified, psql will position the cursor on the 
specified line of the file or query buffer. Note that if a single 
all-digits argument is given, psql assumes it is a line number, not a 
file name.
"

> 
> TIA,
> 
> Rich
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Wed, Feb 12, 2025 at 1:50 PM Rich Shepard <rshepard@appl-ecosys.com>
> wrote:
>> My web searches suggest that using the psql command line I'm limited to
>> moving the cursor one character at a time. Is there a way to use a small
>> editor, e.g., joe on linux, to move by words or to the begining and end of
>> the line?

> The PGDG prebuilt packages are built with libreadline.  Thus, if that's how
> you installed psql, Home, End, Ctrl-Left, Ctrl-Right and Ctrl-w should work.

On my machine, Ctrl-Left/Right don't seem to do anything, but
the usual Emacs combinations Ctrl-A, Ctrl-E, Escape-B, Escape-F
and so on work in psql.  I don't believe I did anything special
to configure that.  Check your local readline documentation.

As Adrian notes, there's also \e, which is good for entering
or editing multi-line queries.

            regards, tom lane

On Wed, 12 Feb 2025, Tom Lane wrote:

> On my machine, Ctrl-Left/Right don't seem to do anything, but the usual
> Emacs combinations Ctrl-A, Ctrl-E, Escape-B, Escape-F and so on work in
> psql. I don't believe I did anything special to configure that. Check your
> local readline documentation.

Tom,

I run psql from a urxvt virtual console, not emacs (which is where I write
scripts.)

> As Adrian notes, there's also \e, which is good for entering
> or editing multi-line queries.

Often when updating table rows I'll modify one string and the row ID so I'll
learn to use \e to move around the string.

Many thanks,

Rich

On 2/12/25 11:44, Rich Shepard wrote:
> On Wed, 12 Feb 2025, Tom Lane wrote:
> 
>> On my machine, Ctrl-Left/Right don't seem to do anything, but the usual
>> Emacs combinations Ctrl-A, Ctrl-E, Escape-B, Escape-F and so on work in
>> psql. I don't believe I did anything special to configure that. Check 
>> your
>> local readline documentation.
> 
> Tom,
> 
> I run psql from a urxvt virtual console, not emacs (which is where I write
> scripts.)

The point Tom was making is that psql uses readline for it's editing 
positioning and that for him Emacs movement commands work in psql.

> 
>> As Adrian notes, there's also \e, which is good for entering
>> or editing multi-line queries.
> 
> Often when updating table rows I'll modify one string and the row ID so 
> I'll
> learn to use \e to move around the string.
> 
> Many thanks,
> 
> Rich
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 12 Feb 2025, Adrian Klaver wrote:

> The point Tom was making is that psql uses readline for it's editing 
> positioning and that for him Emacs movement commands work in psql.

Adrian,

Thanks for clarifying; the emacs commands work for me, too, in psql.

Regards,

Rich

On 2/12/25 12:03, Ron Johnson wrote:
> On Wed, Feb 12, 2025 at 2:29 PM Tom Lane <tgl@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:

> There's got to be something in your shell config which says "use emacs 
> keys" instead of "use vi keys".

 From man readline:

"By default, the line editing commands are similar to those of emacs.  A
  vi-style line editing interface is also available."

To change from the default would involve setting editing-mode in 
/etc/inputrc or  ~/.inputrc.

> 
> -- 
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!

-- 
Adrian Klaver
adrian.klaver@aklaver.com

There's got to be something in your shell config which says "use emacs keys" instead of "use vi keys".

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!

Rob Sargent <robjsargent@gmail.com> writes:
>> There's got to be something in your shell config which says "use emacs 
>> keys" instead of "use vi keys".

> Or something in yours setting vi?

I do have

export EDITOR=emacs

Possibly libreadline is reacting to that, but I've not checked
into it.

            regards, tom lane

On 2/12/2025 11:44, Rich Shepard wrote:
> I run psql from a urxvt virtual console, not emacs (which is where I 
> write scripts.)

As with everything in Emacs, there's a mode for that.

In case you're not aware of it, sql-mode allows you not only to interact
with psql from within Emacs, but also to write SQL queries in a buffer
and then send the whole buffer or the region.

It's not documented in the info pages, but from within Emacs, one of
these should get you started:

  C-h P sql
  M-x sql-help

Or just do: M-x sql-postgres

  - Ben

On Wed, 12 Feb 2025 16:21:21 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:


> I do have
>
> export EDITOR=emacs
>
> Possibly libreadline is reacting to that, but I've not checked
> into it.
>

Emacs combinations work on my machine without it.


--

                                        Bien à vous, Vincent Veyron

https://compta.libremen.com
Logiciel libre de comptabilité générale en partie double

On 2/13/25 09:20, Vincent Veyron wrote:
> On Wed, 12 Feb 2025 16:21:21 -0500
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> 
>> I do have
>>
>> export EDITOR=emacs
>>
>> Possibly libreadline is reacting to that, but I've not checked
>> into it.
>>
> 
> Emacs combinations work on my machine without it.

See my answer here:

https://www.postgresql.org/message-id/4e1966c1-1f24-47e5-9c87-8cf466216b6b%40aklaver.com

for why.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

It appears that the RHEL default /etc/initrc file defines a bunch of escape sequences "for linux console and RH/Debian xterm" cursor keys when in emacs mode.

Our servers are RHEL, my ssh client emulates xterm, and is in the default emacs mode.  Thus, the cursor keys automagically work for me, and I just learned that I've been in emacs mode this whole time!

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!