Searching Command History in PostgreSQL Server

Question

I was wondering if there is a more efficient way than scrolling up every command history to find what I need, such as searching for specific characters in a command when writing in the PostgreSQL server. I am running iTerm in macOS and I know this can be done using Control + R but this does not work when I am writing in the PostgreSQL server. Are there any ways around this?

For example, as I am frequently working with the Apache AGE extension, I will always need to SET the search path and LOAD the extension manually each time the PostgreSQL server is started. I know that the search path can be defined permanently using commands and that the postgresql.conf file can be modified as well, but I just want to know if I can search for specific characters in my command history.

By writing in the PostgreSQL server, I mean when writing in SQL query after running the bin/psql command:

➜  pgsql-12 bin/psql --port=5431 test1
psql (12.14)
Type "help" for help.

test1=#

Please specify what you mean by "writing in the PostgreSQL server". Are you running PostgreSQL in single-user mode? How about putting it in `shared_preload_libraries`? BTW: why are so many people interested in that software all of a sudden? — Laurenz Albe, Mar 08 '23 at 20:18
The search path can be defined on the user permanently: `alter user ... set search_path = ...`. — , Mar 08 '23 at 20:43
1) According to docs [AGE setup](https://age.apache.org/age-manual/master/intro/setup.html) `LOAD 'age';` needs to happen for each connection in which case look at [session_preload_libraries](https://www.postgresql.org/docs/current/runtime-config-client.html). 2) In `postgresql.conf` modify [search_path](https://www.postgresql.org/docs/current/runtime-config-client.html) to have it in place when the server starts. — Adrian Klaver, Mar 08 '23 at 20:59
@Laurenz Albe Writing in PostgreSQL server as in writing in SQL query after running the `bin/psql` command. I am using the software for work and I think others are as well. @Adrian Klaver I know about being able to modify `postgresql.conf`. I was just giving an example of finding a previous command, which I want to do it by searching instead up just pressing the up arrow key to traverse through the entire history. — Ken W., Mar 08 '23 at 21:52
@a_horse_with_no_name I also know about that but as I said, I would just like to search my command history. — Ken W., Mar 08 '23 at 22:01
In that case in `psql` do `\s` which gives you the command history. Then use whatever search command is appropriate for the pager used. In my case `\`. — Adrian Klaver, Mar 08 '23 at 22:48
ctrl-R works fine in psql on Linux. It doesn't in Windows, but I would expect it to work well on Mac though I don't have one to test with. Are you sure it isn't working there? — jjanes, Mar 09 '23 at 00:00
@Adrian this sort of works but I will still need to copy and paste manually. I was hoping it would conveniently appear like when using the up arrow key. — Ken W., Mar 09 '23 at 00:36
@jjanes It's not working for sure which is odd. Other shortcuts like `Control + U` works, however. — Ken W., Mar 09 '23 at 00:38
Then there is something wrong with your installation. If I remember correctly, `psql` should use `libedit` on MacOS. — Laurenz Albe, Mar 09 '23 at 06:37

Ahmar · Accepted Answer · 2023-03-09T02:36:45.237

Update:

One way to get auto suggestions from history is by using pgcli instead of psql

On most terminals ctrl+shift+f or cmd+f opens the find menu:

Alternatively, instead of 'scrolling up' you can use the \s command to print the psql's command line history.

postgresDB=# \s
LOAD 'age';
CREATE EXTENSION age;
SET search_path TO ag_catalog;
\dx

Aditionally, this history can be written to a file using \s filename.

postgresDB=# \s test
Wrote history to file "test".

Now, psql commands in this file (or any other file) can be executed by using \i filename.

postgresDB=# \i test
LOAD
psql:test:2: ERROR:  extension "age" already exists
SET
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 age     | 1.1.0   | ag_catalog | AGE database extension
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

You can read more about these psql commands here

This is definitely a solution but not really what I am looking for as it is such a workaround to get this to work. I was hoping when searching for a previous command, it would conveniently appear in the current line like when using the up arrow key, or using the `Control + R` shortcut. — Ken W., Mar 09 '23 at 00:43
What you're looking for would be called auto suggestions from history. (like [zsh autosuggestions](https://asciinema.org/a/37390)). — Ahmar, Mar 09 '23 at 02:15

score 1 · Answer 2 · answered Mar 08 '23 at 22:06

1

Regarding loading AGE automatically without needing to type it every time, you can go to your database folder and change the postgresql.conf file with shared_preload_libraries = 'age' and also search_path = 'ag_catalog, "$user", public' these variables will be initially set as comments, so it is needed to remove the " # " to apply these changes.

answered Mar 08 '23 at 22:06

Matheus Farias

716
1
10

I am aware of this, but I just want to know if I can search my command history. I apologize for not being clear. – Ken W. Mar 08 '23 at 22:20

Searching Command History in PostgreSQL Server

2 Answers2