This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
To modify the prompt, use \set to change the strings held by the three prompt variables. When defining your prompt strings, use % to designate that you wish for a variable to be substituted into the string (read further for a list of defined substitutions you can make with the % sign). You may also use \n to display new line character. All other characters will be displayed normally. The following example adds a trivial amount of information into the PROMPT1 variable: the prompt will now display the prefix psql .
Example 4-60. Setting the Prompt Variables
testdb=# \set PROMPT1 'psql %/%R%' psql testdb=
Table 4-17. Defined % Substitution Strings
Substitution character | Description |
---|---|
%~ | This will insert the name of the database you are currently working in; however, if you are currently working in the default database, a tilde ( ~ ) will be displayed. |
%# | This will insert a number sign (#) if the current user is defined as a superuser within the database. Otherwise, it will insert a greater-than sign ( > ). |
%> | This will insert the port number the database server is currently accepting connections at. |
%/ | This will insert the name of the database you are currently working in. |
%m | This will insert the hostname of the server the database is currently running on, truncated down to the string before the first dot (i.e., "yourserver.com" would become "yourserver" when inserted). |
%M | This will insert the full hostname of the server the database is currently running on. If no hostname information is available, the string "localhost" will be inserted. |
%n | This will insert the database username you are currently connected as. |
%R | When used with PROMPT1, this will insert an equal sign (=) during normal operation; in single-line mode, it will insert a caret (^); and if your session ever becomes disconnected from the backend process, an exclamation point (!) is inserted. When used with PROMPT2, %R inserts a dash (-) instead of an equal sign during normal operation, and whatever you entered as the end-symbol if you started a new line during an unterminated statement (for example, if you leave a parenthesis open while entering a multi-line query, this variable will display a parenthesis in the prompt). Nothing is inserted if this is used with the PROMPT3 variable. |
%[ number ] | You may enter decimal, octal, or hexidecimal numbers into the prompt variables. To specify whether the number you are entering is octal, prefix it with a 0; to specify the number is hexidecimal, prefix it with a 0x; otherwise it is interepreted as decimal number. |
%:[ variable_name ] | To insert a psql variable, use the colon (:) and the variable's identifier. |
%` command ]` | Inserts the output of whatever command is specified with the command parameter. |
 
Continue to: