client allows you to modify and create variables using the
\set slash command, and delete them with the \unset
slash command. Variables within
the same way as variables within UNIX and Linux shell programs, such as bash.
Though the overall implementation of variables within
simple, they are still useful, as you may easily insert or substitute the values of variables into slash commands and
Note: When setting and using variables, be aware that
uses a set of pre-defined internal
variables. Setting these to non-intended values may cause unpredictable and undesirable effects within the program.
For a list of these variables and their uses, see Appendix D.
To set a variable, use the \set command, giving the command the name and the value
of the variable you wish to set, in sequence, separated by space(s). This will either modify a previously existing variable or
create a new variable if there is not one matching the variable name you supplied). As Example 6-2 shows,
the variable name can be any length, and you can use any combination of letters, underscores, or numbers, and the value of the variable
may be set to a string of any length.
Example 6-2. Setting a variable
\set myvariable 'There are many like it, but this one is mine.'
Now, when you type \set without any arguments, the variable will appear in the
list of variables.
Example 6-3. The variable list
VERSION = 'PostgreSQL 7.1.3 on i586-pc-linux-gnu, compiled by GCC 2.96'
DBNAME = 'testdb'
USER = 'postgres'
PORT = '5432'
ENCODING = 'SQL_ASCII'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
HISTSIZE = '500'
myvariable = 'There are many like it, but this one is mine.'
Once you have defined a variable, you can use what is known as
to place it within both internal slash commands and SQL commands. This makes
it possible to do things like load files into variables, and then use the loaded contents during an
INSERT or SELECT, as well as more basic substitutions.
To substitute a variable value in this way,
prefix the variable name with a colon (:) when you reference it from within other statements.
For example, Example 6-4 demonstrates
how to use a created variable during an INSERT or SELECT
Example 6-4. Using interpolation during an INSERT
\set manager_id 150
INSERT INTO employees VALUES (
SELECT * FROM employees WHERE id = :manager_id;
firstname | lastname | id
Kevin | Murphy | 150
As mentioned, it is possible to insert files into variables and then use interpolation to
insert their content into other commands. To read files, use backticks (`)
to set a variable to the output of the
command (the UNIX command to display the contents
of a file). Example 6-5 and Example 6-6
illustrate a basic way of doing this. In these examples, the
file is located in
the user's home directory (
Example 6-5. Reading from a file into a variable
\set data `cat tabledata`
'Mike', 'Nelson', 151
Example 6-6. Using a variable in an INSERT
INSERT INTO employees VALUES (:data);
After Example 6-6, you would have a new row within the
employees table with the values set in the data variable.