Appendix D. Internal psql Variables

The psql client uses a variety of internal variables as special system variables to control aspects of the program. A few of the most notable variables are PROMPT1, PROMPT2, and PROMPT3, which store the prompts for the program. While running the program you can set and unset these variables at will using the \set and \unset commands. A list of all the special variables psql uses follows:

This variable holds the name of the database psql is currently connected to. This variable is set whenever psql connects to a database, either when starting up or when instructed to connect during program operation.

This variable controls what gets displayed on the screen when executing commands from a file. To display all contents of a script file on the screen as it is parsed, set this variable to all. To display all queries as they are sent to the backend process, set this variable to queries.

ECHO_HIDDEN

This variable, when set to true, displays the queries used by slash commands from within psql. Such queries will be displayed before they are sent to the backend. To show the queries for slash commands without actually executing them, set ECHO_HIDDEN to noexec.

This variable holds the database’s multibyte encoding scheme. You must have compiled PostgreSQL to support multibyte encoding; if you did not, this variable will contain SQL_ASCII.

HISTCONTROL

This variable sets methods of controlling the psql history buffer. Set this variable to ignorespace if you wish for the history to ignore all lines entered that were preceded by spaces. Set it to ignoredups to ignore any entries that matched the previous line entered. To ignore both lines beginning with spaces and lines that duplicate, use the value ignoreboth.

This variable sets the length of the history buffer; the default length is 500 lines.

This variable holds the hostname of the database server you are currently connected to. This value is set during startup and whenever a database connection occurs.

IGNOREEOF

This variable controls how psql handles EOF characters. Normally, when psql receives an EOF character the application terminates. This character is usually generated by pressing CTRL-D on the keyboard. Setting this option to any non-numeric value will inform psql that you wish to have the EOF character ignored until it is repeated more than 10 times. You may alternatively set this variable to a specific number; if you do so, psql will ignore that many EOF characters before terminating.

This variable contains the last object identifier (OID) set from an INSERT command, or lo_import() function call.

LO_TRANSACTION

This variable sets the action psql will take during large object operations. It may be set to one of the following values:

rollback: This causes any transaction you are currently working within to be rolled back if you attempt an operation on a large object (or a large object import). For maximum efficiency, large object operations should usually be placed within their own transactions; for this reason, LO_TRANSACTION defaults to rollback.
commit: This causes psql to commit any transaction you were in before you issued a large object operation.
nothing: This causes psql to execute the large object operation within the current transaction.

ON_ERROR_STOP: This variable, when set (to any value), causes psql to terminate the processing of a script that encounters an error (such as incorrect SQL syntax or misuse of a slash command), instead of continuing to process it. By default, scripts that have encountered errors continue to be processed by psql.
PORT: This variable holds the port number that you are currently connected to. This value is set automatically both when you start the program and when you manually connect to a database from the psql prompt.
PROMPT1, PROMPT2, PROMPT3: These variables hold character strings that directly control the prompt’s structure within psql. Setting these will change the way each prompt is displayed within the program. See Chapter 6, for information on how to set these variables.
SINGLELINE: This variable, when set (to any value), causes SQL input to psql to be executed when a newline is reached, without the need for a semi-colon or \g terminator. This mode can also be set by the command line option -S.
SINGLESTEP: This variable, when set (to any value), causes each statement to require confirmation before being executed.
USER: This variable holds the PostgreSQL username you are connected to the database with.