The psql client allows you to modify and create variables using the \set slash command and delete them with the \unset slash commands. Variables within psql work much the same way as variables within UNIX and Linux shell programs, such as bash . Though the overall implimentation of variables within psql is fairly simple, they are still useful, as you may insert the values of variables into slash commands and SQL commands.

	Internal Variables
	When setting and using variables, remember that psql 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 commands and their uses, see the reference section.

To set a variable, use the \set command, giving it the name and the value of the variable you wish to set (this will either modify a previously existing variable or create a new variable if there is not one matching the variable name you supplied). The name of the variable can be of any length and any combination of letters, underscores, and numbers. The value of the variable may be set to a string of any length.

Example 4-51. Setting a Variable

testdb=# \set variablename 'This is my variable.' 

Now, when you type \set without any arguments, the variable will appear in the list of variables.

Example 4-52. The Variable List

testdb=# \set
VERSION = 'PostgreSQL 7.1.2 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'
variablename = 'This is my variable.'

Once you have a variable defined, you can use what is known as interpolation to use it within both internal slash commands and SQL commands. This makes it possible to do things like load files into variables, then use the loaded contents during an INSERT or SELECT , as well as more basic substitution. To do this, prefix the variable name with a colon (:) when you reference it from within other commands. For example, to use a created variable during an INSERT command and SELECT command, refer to the following examples.

Example 4-53. Using Interpolation During an INSERT

testdb=# \set manager_id 150
testdb=# INSERT INTO employees VALUES (
testdb(#  'James',
testdb(#  'Well',
testdb(#  :manager_id
testdb(# );

Example 4-54. Using Interpolation During a SELECT

testdb=# SELECT * FROM employees WHERE id = :manager_id;
 firstname | lastname | id
 -----------+----------+-----
  James     | Well     | 150
  (1 row)

As mentioned, it is also possible to insert files into variables and then use interpolation to insert their content into other commands. To read files, use backticks (`) to substitute command output of cat . The following examples illustrate a basic way of doing this. In the example, the tabledata file is located in the user's home directory (~/).

Example 4-55. Reading from a File into a Variable

testdb#= \set data `cat tabledata`
testdb=# \echo :data
'Alexander', 'Ross', 160                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

Example 4-56. Using a Variable in an INSERT

testdb=# INSERT INTO employees VALUES (:data);

You should now have a new row within the employees table with the values set in the data variable.