ybsql User Variables ​

You can set ybsql properties and user-defined variables in your startup file or during a ybsql session. Properties and variables are name/value pairs, where the value can be any string of any length. The name must consist of letters, digits, and underscores.

To set a variable, use the \set command. To see the its value, use the \echo command with a colon in front of the variable name. To interpolate a value in a SQL statement, again enter a colon in front of the name.

The value of a ybsql variable or property is copied literally and may contain unbalanced quotes, or even backslash commands. Therefore, you need to place variables and properties carefully in the context of other SQL syntax. When you want a variable value to be used as an SQL literal or identifier, quoting is recommended. To quote the value of a variable as an SQL literal, put the variable name in single quotes. To quote the value as an SQL identifier, put the variable name in double quotes.

For example, create (or set) a user variable t1 and reference it in a query.

premdb=# \set t1 'match'
premdb=# select * from :"t1" limit 2;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
      21 | 2012-12-22 00:00:00 |   44 |   78 | 2-1     | 1-1
      21 | 2012-10-06 00:00:00 |   44 |   82 | 3-2     | 2-1
(2 rows)

If you do not specify a value in the \set command, the property or variable is set to an empty string.

To unset (or delete) a variable or property, use the \unset command. To show the values of all properties and variables, run \set without any arguments. For example:

premdb=# \set
AUTOCOMMIT = 'on'
...
DBNAME = 'premdb'
USER = 'yb100'
PORT = '5432'
ENCODING = 'UTF8'
HISTFILE = '/home/yb100/newybsqlhist'
...
db2 = 'premdb2'

See also \gset and \prompt in ybsql Slash (\) Commands.

Parent topic:ybsql Properties and Variables