PostgreSQL Technologies

Customize PostgreSQL prompt with psqlrc file

psqlrc file customizes the behaviour of the psql interactive command line prompt. This file comes in three variations as follows:

  1. The system-wide startup file is named psqlrc and is sought in the installation’s “system configuration” directory, identified by running pg_config –sysconfdir. The name of this directory can be set explicitly via the PGSYSCONFDIR environment variable.
  2. The user’s personal startup file is named .psqlrc and is sought in the invoking user’s home directory. On Windows, which lacks such a concept, the personal startup file is named %APPDATA%\postgresql\psqlrc.conf. The location of the user’s startup file can be set explicitly via the PSQLRC environment variable.
  3. Both the system-wide startup file and the user’s personal startup file can be made psql-version-specific by appending a dash and the PostgreSQL major or minor release number to the file name, for example ~/.psqlrc-9.2 or ~/.psqlrc-9.2.5.

Edit the psql prompt(PROMPT1):

Go to user’s home location
ex: /home/postgresql (user is postgresql)
cd /home/postgresql
vi ~/.psqlrc
\set PROMPT1 '%M:%> %n@%/%R%#%x '

[OR]
\set PROMPT1 '%M:%[%033[1;31m%]%>%[%033[0m%] %n@%/%R%#%x '


If logged into a machine with hostname “Besttech”, as user “Kishore” and accessing the database “edb” as a regular user, you will see

[Besttech]:5432 Kishore@edb=>


Prompting settings:

%M
The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket, or [local:/dir/name], if the Unix domain socket is not at the compiled in default location.

%m
The host name of the database server, truncated at the first dot, or [local] if the connection is over a Unix domain socket.

%>
The port number at which the database server is listening.

%n
The database session user name. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)

%/
The name of the current database.

%~
Like %/, but the output is ~ (tilde) if the database is your default database.

%#
If the session user is a database superuser, then a #, otherwise a >. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)

%p
The process ID of the backend currently connected to.

%R
In prompt 1 normally =, but @ if the session is in an inactive branch of a conditional block, or ^ if in single-line mode, or ! if the session is disconnected from the database (which can happen if \connect fails). In prompt 2 %R is replaced by a character that depends on why psql expects more input: - if the command simply wasn't terminated yet, but * if there is an unfinished /* … */ comment, a single quote if there is an unfinished quoted string, a double quote if there is an unfinished quoted identifier, a dollar sign if there is an unfinished dollar-quoted string, or ( if there is an unmatched left parenthesis. In prompt 3 %R doesn't produce anything.

%x
Transaction status: an empty string when not in a transaction block, or * when in a transaction block, or ! when in a failed transaction block, or ? when the transaction state is indeterminate (for example, because there is no connection).

%l
The line number inside the current statement, starting from 1.

%digits
The character with the indicated octal code is substituted.

%:name:
The value of the psql variable name. See the section Variables for details.

%command
The output of command, similar to ordinary “back-tick” substitution.

%[ … %]
Prompts can contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with %[ and %].

Colors :

Please find the below values for various colours:

32 for green
33 for yellow
34 for blue
35 for magenta
36 for cyan
37 for white

PS : After editing psqlrc, by default it prints welcome messages and various informational output. If you want to hide these set the QUIET flag at the top and bottom of the psql file.

\set QUIET 1
\pset border 2
\timing
\set QUIET 0
\set PROMPT1 '%M:%> %n@%/%R%#%x '
\set PROMPT1 '%M:%[%033[1;31m%]%>%[%033[0m%] %n@%/%R%#%x '


For more detailed settings/options, click here… (under section: Prompting)


Comments Rating 0 (0 reviews)

About the author

BestTechReads

Add Comment

Click here to post a comment

Sending

Categories

Categories