psql command line editing

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.

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

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 Wed, Feb 12, 2025 at 2:29 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

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!

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!

Or something in yours setting vi?

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:

/messages/by-id/4e1966c1-1f24-47e5-9c87-8cf466216b6b@aklaver.com

for why.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, Feb 12, 2025 at 3:29 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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.

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!

On 2/13/25 12:11, Ron Johnson wrote:

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!

We're an ancient -not to say insidious- cult.