Advanced Features
Variables
psql provides variable substitution features similar to common UNIX command shells. This feature is new and not very sophisticated, yet, but there are plans to expand it in the future. Variables are simply name/value pairs, where the value can be any string of any length. To set variables, use the psql meta-command \set:
testdb=> \set foo bar |
testdb=> \echo :foo bar |
 | The arguments of \set are subject to the same substitution rules as with other commands. Thus you can construct interesting references such as \set :foo 'something' and get "soft links" or "variable variables" as in Perl or PHP, respectively. Unfortunately, there is no way to do anything useful with these constructs. On the other hand, \set bar :foo is a perfectly valid way to copy a variable. |
If you call \set without a second argument, the variable is simply set, but has no value. To unset (or delete) a variable, use the command \unset.
psql's internal variable names can consist of letters, numbers, and underscores in any order and any number of them. A number of regular variables are treated specially by psql. They indicate certain option settings that can be changed at runtime by altering the value of the variable or represent some state of the application. Although you can use these variables for any other purpose, this is not recommended, as the program behavior might grow really strange really quickly. By convention, all specially treated variables consist of all upper-case letters (and possibly numbers and underscores). To ensure maximum compatibility in the future, treat these variables as reserved. A list of all specially treated variables follows.
- DBNAME
The name of the database you are currently connected to. This is set every time you connect to a database (including program start-up), but can be unset.
- ECHO
If set to "all", all lines entered or from a script are written to the standard output before they are parsed or executed. To specify this on program start-up, use the switch -a. If set to "queries", psql merely prints all queries as they are sent to the backend. The option for this is -e.
- ECHO_HIDDEN
When this variable is set and a backslash command queries the database, the query is first shown. This way you can study the PostgreSQL internals and provide similar functionality in your own programs. If you set the variable to the value "noexec", the queries are just shown but are not actually sent to the backend and executed.
- ENCODING
The current client multibyte encoding. If you are not set up to use multibyte characters, this variable will always contain "SQL_ASCII".
- HISTCONTROL
If this variable is set to ignorespace, lines which begin with a space are not entered into the history list. If set to a value of ignoredups, lines matching the previous history line are not entered. A value of ignoreboth combines the two options. If unset, or if set to any other value than those above, all lines read in interactive mode are saved on the history list.
- HISTSIZE
The number of commands to store in the command history. The default value is 500.
- HOST
The database server host to which you are currently connected. This is set every time you connect to a database (including program start-up), but can be unset.
- IGNOREEOF
If unset, sending an EOF character (usually Control-D) to an interactive session of psql will terminate the application. If set to a numeric value, that many EOF characters are ignored before the application terminates. If the variable is set but is not numeric, the default is 10.
- LASTOID
The value of the last affected oid, as returned from an INSERT or lo_insert command. This variable is only guaranteed to be valid until after the result of the next SQL command has been displayed.
- LO_TRANSACTION
If you use the PostgreSQL large object interface to specially store data that does not fit into one tuple, all the operations must be contained in a transaction block. (See the documentation of the large object interface for more information.) Since psql has no way to tell if you already have a transaction in progress when you call one of its internal commands (\lo_export, \lo_import, \lo_unlink) it must take some arbitrary action. This action could either be to roll back any transaction that might already be in progress, or to commit any such transaction, or to do nothing at all. In the last case you must provide your own BEGIN TRANSACTION/COMMIT block or the results will be unpredictable (usually resulting in the desired action's not being performed in any case).
To choose what you want to do you set this variable to one of "rollback", "commit", or "nothing". The default is to roll back the transaction. If you just want to load one or a few objects this is fine. However, if you intend to transfer many large objects, it might be advisable to provide one explicit transaction block around all commands.
- ON_ERROR_STOP
By default, if non-interactive scripts encounter an error, such as a malformed SQL query or internal meta-command, processing continues. This has been the traditional behavior of psql but it is sometimes not desirable. If this variable is set, script processing will immediately terminate. If the script was called from another script it will terminate in the same fashion. If the outermost script was not called from an interactive psql session but rather using the -f option, psql will return error code 3, to distinguish this case from fatal error conditions (error code 1).
- PORT
The database server port to which you are currently connected. This is set every time you connect to a database (including at program start-up), but can be unset.
- PROMPT1, PROMPT2, PROMPT3
These specify what the prompt psql issues is supposed to look like. See Prompting, below.
- QUIET
This variable is equivalent to the command line option -q. It is probably not too useful in interactive mode.
- SINGLELINE
This variable is set by the command line option -S. You can unset or reset it at run time.
- SINGLESTEP
This variable is equivalent to the command line option -s.
- USER
The database user you are currently connected as. This is set every time you connect to a database (including program start-up), but can be unset.
SQL Interpolation
An additional useful feature of psql variables is that you can substitute ("interpolate") them into regular SQL statements. The syntax for this is to prepend the variable name with a colon (:). For example:
testdb=> \set foo 'my_table' testdb=> SELECT * FROM :foo; |
A popular application of this facility is to refer to the last inserted OID in subsequent statements. This enables you to build a foreign key scenario. Another possible use is to copy the contents of a file into a field. In this example you first load the file into a variable:
testdb=> \set content '\'' `cat my_file.txt` '\'' testdb=> INSERT INTO my_table VALUES (:content); |
testdb=> \set content `sed -e "s/'/\\\\\\'/g" < my_file.txt` |
As colons may legally appear in queries, the following rule applies: if the variable is not set, the character sequence "colon+name" is not changed. However, you can also escape a colon with a backslash to protect it from interpretation. (The colon syntax for variables is standard SQL for embedded query languages, such as ecpg. The colon syntax for array slices and type casts are PostgreSQL extensions, hence the conflict.)
Prompting
The prompts that psql issues can be customized to your preference. The three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt. PROMPT1 is the normal prompt that is issued when psql requests a new query. PROMPT2 is issued when more input is expected during query input because the query was not terminated with a semicolon or a quote was not closed. PROMPT3 is issued when you run an SQL COPY command and you are expected to type in the tuples on the terminal.
The value of the respective prompt variable is printed literally, except where a percent sign ("%") is encountered. Depending on the next character, certain other text is substituted instead. Defined substitutions are:
- %M
The full hostname (with domain name) of the database server (or "localhost" if hostname information is not available).
- %m
The hostname of the database server, truncated after the first dot.
- %>
The port number at which the database server is listening.
- %n
The database username you are connected as. (This is not your Linux user name)
- %/
The name of the current database.
- %~
Like %/, but the output is "~" (tilde) if the database is your default database.
- %#
If the current user is a database superuser, then a "#", otherwise a ">".
- %R
In PROMPT1 this is normally "=", but is set to "^" if in single-line mode, and "!" if the session is disconnected from the database (which can happen if \connect fails). In PROMPT2 the sequence is replaced by "-", "*", a single quote, or a double quote, depending on whether psql expects more input because the query was not terminated yet, because you are inside a /* ... */ comment, or because you are inside a quote. In PROMPT1 the %R does not resolve to anything.
- %digits
If digits starts with 0x the rest of the characters are interpreted as a hexadecimal digit and the character with the corresponding code is substituted. If the first digit is 0 the characters are interpreted as on octal number and the corresponding character is substituted. Otherwise a decimal number is assumed.
- %: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.
Miscellaneous
psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own (out of memory, file not found) occurs, 2 if the connection to the backend went bad and the session is not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.
Before starting up, psql attempts to read and execute commands from the file $HOME/.psqlrc. It could be used to set up the client or the server to taste (using the \set and SET commands).
GNU Readline
psql supports the readline and history libraries for convenient line editing and retrieval. The command history is stored in a file named .psql_history in your home directory and is reloaded when psql starts up. Tab-completion is also supported, although the completion logic makes no claim to be an SQL parser. When available, psql is automatically built to use these features. If for some reason you do not like the tab completion, you can turn it off in psql by putting this in a file named .inputrc in your home directory:
$if psql set disable-completion on $endif |