Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page

About the psql Prompt

The psql client supports the complete modification of its prompt. This can be helpful for displaying various pieces of information in an obvious way (what could be more obvious than the prompt?). Prompt information is stored in the PROMPT1, PROMPT2, and PROMPT3 variables within psql . The program displays each of these variables at different times.

PROMPT1 contains the normal (default) prompt information while PROMPT2 contains the prompt information that is displayed on a new line during a statement or query that you have not yet terminated (because you have not ended it with either a semicolon or issued the \g command) PROMPT3 contains the prompt information displayed while entering data during an SQL COPY command. To view how your prompts are currently configured, use the \set command without arguments to view a list of defined variables. Within this list you should see entries for PROMPT1, PROMPT2, and PROMPT3. You'll see single quotes surrounding user-configurable display strings, which define how the psql prompt appears. The %-prefixed characters (e.g., %m) are variables; all other characters are printed directly as shown.

Table 6-1 displays the default prompt settings for each of the prompt variables. Notice that the display in the second row, PROMPT2, assumes that a query has been continued to the next line with an open parenthesis, resulting in the ( symbol preceding the hash mark (#).

Table 6-1. Default PROMPT settings

Prompt Variable Display
PROMPT1 '%/%R%#' testdb=#
PROMPT2 '%/%R%# ' testdb(#
PROMPT3 '>> ' >>

Modifying the prompt

To modify the psql prompt, use \set to change the strings held by the three prompt variables. When defining your prompt strings, use % to substitute a variable into the string (Example 6-7 provides a list of defined substitutions you can make with the % sign). You may use \n to display a new line character. All other characters will be displayed normally. Example 6-7 modifies the PROMPT1 variable to contain an additional psql: prefix, trivially modifying the standard prompt display.

Example 6-7. Setting the prompt variables

\set PROMPT1 'psql:%/%R%# '


Table 6-2. Prompt substitution characters

Substitution character



This inserts the name of the database you are currently working in. 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.


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., "" would become "yourserver" when inserted).


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.


This will insert the database username you are currently connected as.


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 multiline 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 specific characters in prompt variables using decimal, octal, or hexadecimal numbers. To specify an octal number , prefix it with a 0; to specify the number as hexadecimal, prefix it with a 0x; otherwise number is interpreted as a decimal number.

%: variable

To insert the contents of a psql variable , use the colon (:) and the variable's identifier.

%` command `

Inserts the output of whatever command is specified with the command parameter.

Prompt examples

Using the \set command, you may combine the different substitution characters to form whatever prompt you would like. Example 6-8 and Example 6-9 demonstrate setting the PROMPT1 variable to an arbitrary new sequence.

Example 6-8. Customizing the prompt with database host, port, and username

\set PROMPT1 '[%m:%>:%n](%/)= '


Example 6-9. Customizing the prompt with the date, database name, and username

\set PROMPT1 '\n[%`date`]\n%n:%/%=# '

[Fri Aug  3 21:44:30 PDT 2001]
Databases - Practical PostgreSQL
Previous Page Home Next Page

  Published under the terms of the Open Publication License Design by Interspire