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:

DBNAME

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.

ECHO

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.

ENCODING

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.

HISTSIZE

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

HOST

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.

LASTOID

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.