ybsql Properties ​

The following properties are predefined options that you can set at run time or in your ybsql startup file (.ybsqlrc). By convention, these properties have uppercase names; avoid using the same names in your user-defined variables. For more information about each property, see Property Details.

To set a property or a variable, use the \set command. To see the its value, use the \echo command with a colon in front of the property or variable name. For example, set the HISTSIZE property and return its value:

premdb=> \set HISTSIZE 100
premdb=> \echo :HISTSIZE
100

Property Details ​

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 name is .ybsql_history (or ybsql_history on Windows). The file is found in the user's home directory on Linux clients and in %APPDATA%\postgresql on Windows clients.

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 Prompt Variables 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