Question 89463 in PostgreSQL Question posted in
The official documentation can be found here.

Searching Command History in PostgreSQL Server

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=#
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    Update:

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

    pgcli autocomplete

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search