ybsql Variables

You can set variables in your startup file or during a ybsql session. 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 current value of a variable use the \echo command with a colon in front of the variable name. For example:
premdb=> \set HISTSIZE 100
premdb=> \echo :HISTSIZE
100

If you do not specify a value in the \set command, the variable is set to an empty string. To unset (or delete) a variable, use the \unset command. To show the values of all variables, run \set without any arguments.

Variables for Option Settings

The following variables identify specific option settings that you can change at run time or set in your startup file (.ybsqlrc). By convention, these variables have uppercase names; avoid using the same names in your own user-defined variables.
AUTOCOMMIT
Values are on (the default) and off. AUTOCOMMIT on means that each SQL command is automatically committed when it completes. AUTOCOMMIT off means that SQL commands are not committed until a COMMIT or END statement is run explicitly.
For example:
premdb=# \set AUTOCOMMIT off
premdb=# update season set seasonid=30 where winners is null;
UPDATE 1
premdb=# end;
COMMIT

When AUTOCOMMIT is off, you must explicitly abandon any transaction that fails by using an ABORT or ROLLBACK command. If you exit from a session without committing a transaction, all of the changes are lost.

Warning: Yellowbrick Data recommends never setting AUTOCOMMIT to off. Leaving transactions open can have a serious impact on the entire system, such as preventing locks from being released and causing excessive resource consumption.
DBNAME
Name of the currently connected database, which is set on every connection but can be unset.
ECHO
Set to all, queries, or errors. These values are equivalent to the -a, -e, and -b options, respectively. If unset, or set to none, no queries are displayed.
ENCODING
The current client character set encoding.
FETCH_COUNT
An integer value that defines how many query result rows to fetch and display at a time. By default, ybsql fetches the entire result set before displaying it.
HISTCONTROL
ignorespace: lines that begin with a space are not saved in the history list. ignoredups: lines that match the previous history line are not saved. ignoreboth is a combination of the other two options. If this variable is unset or set to none, all lines read in interactive mode are saved.
HISTFILE
Name of the file where the history list is stored. The default value is .ybsql_history in the user's home directory on the client system.
HISTSIZE
The number of commands to store in the command history. The default value is 500.
HOST
Database server host for the current connection, which is set on every connection but can be unset.
IGNOREEOF
Unset: sending an EOF character (Control+D) to an interactive ybsql session causes the application to quit. Set to a numeric value: number of EOF characters that are ignored before ybsql quits. If the variable is set but has no numeric value, the default is 10. In the following example, the user enters Control+D three times before ybsql quits:
premdb=# \set IGNOREEOF 3
premdb=# Use "\q" to leave ybsql.
premdb=# Use "\q" to leave ybsql.
premdb=# \q
ybuser1@ybuser1:/usr/bin$
ON_ERROR_ROLLBACK
on: if a statement in a transaction block generates an error, ignore the error and continue. interactive: ignore errors only in interactive sessions, not when reading scripts. Unset or off: a statement in a transaction block that generates an error aborts the whole transaction.
ON_ERROR_STOP
By default, command processing continues when an error occurs. on or true: processing stops immediately. In interactive mode, ybsql returns to the command prompt; otherwise, ybsql exits, returning error code 3 to distinguish this case from fatal error conditions, which are reported using error code 1. In either case, any running scripts (the top-level script and any other scripts that it may have invoked) terminate immediately. If the top-level command string contained multiple SQL commands, processing stops with the current command.
For example, if the user's startup file (.ybsqlrc) contains \set ON_ERROR_STOP true and a SQL script returns an error because a table specified in a command does not exist, the script stops running (and returns error code 3):
yellowbrick=# \i premdb.ddl
DROP DATABASE
CREATE DATABASE
You are now connected to database "premdb" as user "yb100".
ybsql:premdb.ddl:7: ERROR:  table "season" does not exist
premdb=# \q
If ON_ERROR_STOP is not set or set to false, the script reports errors but keeps running:
yellowbrick=# \i premdb.ddl
DROP DATABASE
CREATE DATABASE
You are now connected to database "premdb" as user "yb100".
ybsql:premdb.ddl:7: ERROR:  table "season" does not exist
ybsql:premdb.ddl:8: ERROR:  table "team" does not exist
ybsql:premdb.ddl:9: ERROR:  table "hometeam" does not exist
ybsql:premdb.ddl:10: ERROR:  table "awayteam" does not exist
ybsql:premdb.ddl:11: ERROR:  table "match" does not exist
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
...
PORT
Database server port for the current connection, which is set on every connection but can be unset.
PROMPT1, PROMPT2, PROMPT3
Strings that define the appearance of the ybsql prompt. PROMPT1 is the normal prompt that is issued when ybsql starts or is ready for a new command.

PROMPT2 is returned when a command is incomplete: for example, because it does not end with a semicolon or is missing a closing quote. Almost any text string enclosed by single quotes that you enter for the prompt value will be displayed as is. However, you can use a number of % escape sequences to plug in specific values for the current user, host, database, and so on.

PROMPT3 is returned during COPY...FROM STDIN operations.

See ybsql Prompts for examples and details about customizing prompts.
QUIET
on is equivalent to the -q option.
SINGLELINE
on is equivalent to the -S option.
SINGLESTEP
on is equivalent to the -s option.
USER
Database user for the current connection, which is set on every connection but can be unset.
VERBOSITY
default, terse, or verbose error messages. For example:
premdb=# \set VERBOSITY verbose
premdb=# select * from not_a_table;
ERROR:  42P01: relation "not_a_table" does not exist
LINE 1: select * from not_a_table;
                      ^
premdb=# \set VERBOSITY terse
premdb=# select * from not_a_table;
ERROR:  relation "not_a_table" does not exist at character 15