Examples with ybsql Variables ​

This section contains some examples of best practices for setting user variables in ybsql.

Handling space characters within \set command values ​

If the value for a variable does not start one space after the variable name in the \pset command, or if the value contains spaces, enclose the value in single quotes. For example, the value Arsenal below works fine without quotes (value = one word, no spaces):

premdb=> \set team Arsenal
premdb=> select nickname from team where name = :'team';
nickname
----------
Gunners
(1 row)
premdb=> \echo :team
Arsenal

However, look what happens when the value contains spaces:

premdb=> \set team Stoke City
premdb=> \echo :team
StokeCity

The space between the words in the value is skipped, causing the variable to be set in an unexpected way. To solve this problem, use single quotes for the value:

premdb=# \set team 'Stoke City'
premdb=# \echo :team
Stoke City

Escaping embedded single quotes ​

You can escape literal single quotes that appear inside variable values by using a pair of single quotes within the single-quoted string. For example, compare the results of the following \pset commands:

premdb=# \set message Don't run this query
unterminated quoted string
premdb=# \set message Don''t run this query
premdb=# \echo :message
Dontrunthisquery
premdb=# \set message 'Don''t run this query'
premdb=# \echo :message
Don't run this query

Using concatenated variables to create and populate a table ​

This example begins by setting up two variables: db and start_time. This is done in two steps, by running a query that names db and start_time as column aliases, then using the \gset command. The \echo command returns the current values of the two variables.

premdb=# select current_database() as db, date_trunc('secs',current_timestamp::timestamp) as start_time;
   db   |     start_time      
--------+---------------------
 premdb | 2020-07-24 17:28:29
(1 row)

premdb=# \gset
premdb=# \echo Started at :start_time in database :db
Started at 2020-07-24 17:29:45 in database premdb

Now create a table with columns that you can populate with the current values of the variables:

premdb=# create table job_log (start_time timestamp, db_name varchar(128));
CREATE TABLE
premdb=# \echo Started at :start_time in database :"db"
Started at 2020-07-24 17:35:14 in database "premdb"
 
premdb=# insert into job_log values (:'start_time',:'db');
INSERT 0 1

premdb=# select * from job_log;
    start_time      | db_name 
---------------------+---------
 2020-07-24 17:35:14 | premdb
(1 row)

Using backticks to set variable values on Linux clients ​

If you are running ybsql on a Linux client system, you can take advantage of the backtick character (`), as shown in the following examples. You can use backticks to assign the output of a shell command to a variable.

This example sets the shell language based on the results of the shell echo command:

premdb=> \set shell_lang `echo $LANG`
premdb=> \echo :shell_lang
C.UTF-8

This example uses a variable to insert the contents of a file into a table column. Load the file into a variable, then refer to the variable as a quoted string. For example:

premdb=# \set content `cat /home/premdata/seasonid.txt`
premdb=# insert into season(seasonid) values(:'content');
INSERT 0 1

Parent topic:ybsql Properties and Variables