Product Documentation

Appearance

ybsql Slash (\) Commands ​

Within a ybsql session, you can use a number of "backslash commands" (which begin with \) to access, format, and return data. Many of these commands have equivalent command-line options. See also ybsql Command-Line Options and ybsql Examples.

\a

Switch the output format between aligned and unaligned. If the format is unaligned, switch to aligned. If it is aligned, switch to unaligned.

\c, \connect [ dbname [ username ] [ host ] [ port ] ]

Connect to another database.

premdb=# \c yellowbrick bobr localhost 5432
Password for user bobr: 
You are now connected to database "yellowbrick" as user "bobr" on host "localhost" at port "5432".
\C title

Use the specified title for the results of queries. To unset a title, enter \C by itself. This command is equivalent to \pset title title.

\cd [ directory ]

Change the current working directory to the specified directory (without leaving the ybsql session). Entering \cd without a directory changes to the current user's home directory. To display your current working directory, enter \! pwd.

\conninfo

Return information about the current database connection.

\copy

See ybsql \copy Command.

\d [ table | view | sequence ]

List tables, views, and sequences. The \d output for a specific table or view includes attributes such as the distribution type, column constraints, sort or cluster columns, and partitioning columns.

For example:

premdb=# \d matchstats
            Table "public.matchstats"
  Column  |           Type           | Modifiers 
----------+--------------------------+-----------
 matchid  | bigint                   | not null
 matchday | timestamp with time zone | not null
 htid     | smallint                 | 
 atid     | smallint                 | 
 moment   | character varying(5)     | 

Distribution: Hash (matchid)
Clustered Columns: (htid, atid)
Columns:
    matchid UNIQUE

Use \d+ to return more details about tables and other objects that have \d commands.

Note: The \d command does not return a persistent table if a temporary table with the same name exists. The command returns only the temporary table.

See also Patterns in \d Commands.

\ddp

List default access privileges that have been defined for objects owned by roles and users that belong to those roles. See ALTER DEFAULT PRIVILEGES. For each role, this command returns a string of one-letter abbreviations like this:

role=arwdD

The letters correspond to the following privileges:

  • a: INSERT
  • c: CONNECT
  • C: CREATE
  • d: DELETE
  • D: TRUNCATE
  • r: SELECT
  • T: TEMPORARY
  • U: USAGE
  • w: UPDATE
  • x: REFERENCES

For example:

premdb=# \ddp
                   Default access privileges
    Owner    | Schema | Type  |        Access privileges        
-------------+--------+-------+---------------------------------
 yellowbrick |        | table | =r/yellowbrick                 +
             |        |       | yellowbrick=arwdDxt/yellowbrick+
             |        |       | bobr=a/yellowbrick
(1 row)
\dfp [ procedure ]

List stored procedures. (Use \dfp+ to return the source code for the procedure.)

\dg [ role ] (same as \du)

List roles (users).

\dn [ schema ]

List schemas.

\dp [ table | view ]

List privileges on tables, views, and sequences. See also \ddp.

\ds [ sequence ]

List sequences.

\dS [ table | view ]

List system tables and views.

\dt

List tables only.

\du [ role ]

List roles (users). (Same as \dg.)

\dv [ view ]

List views.

\dvS [ view ]

List system views.

\dwp [ profile ]

List WLM profiles.

\dwr [ rule ]

List WLM rules. For example:

premdb=# \dwr slowlane
                             List of WLM rules
   Name   | Order |  Type   | Enabled? | Superuser? |  Profile   | Version 
----------+-------+---------+----------+------------+------------+---------
 slowlane |   100 | prepare | yes      | yes        | shortquery | 
(1 row)

Use \dwr+ to return the JavaScript definition of each rule.

\dwrp [ pool ]

List WLM resource pools.

\e, \edit [ filename ] [ line_number ]

Open the default editor (vi on Linux, notepad.exe on Windows). If you specify a file to edit, when the editor exits, its content is copied into the query buffer. If you do not specify a file, the current query buffer is copied to a temporary file that you can edit. The query buffer is parsed as a single line, so you should use \i if you want to edit a script. If the query in the buffer ends with, or contains, a semicolon, ybsql runs the query immediately. If not, it waits in the query buffer. You can type a semicolon or \g to run it, or \r to cancel.

If you specify a line number, ybsql places the cursor on the specified line of the file or query buffer. For example, go to line 8500 of the file match.csv:

premdb-# \e /home/premdb/match.csv 8500
...
\echo text [ … ]

Print the specified text. If you specify multiple text entries, they are separated in the output by one space, with a newline at the end. This command may be useful for including information within the output of a script. If you use the \o command to redirect query output, consider using \qecho instead of this command.

\encoding [ encoding ]

Return or set the client character set encoding. For example:

premdb=# \encoding latin9
premdb=# \encoding
LATIN9
\f [ string ]

Define the field separator for unaligned query output (by default: |). Use \f by itself to display the current separator.

\g [ filename ], \g [ | command ]

Execute the current query input buffer. Optionally, store the query's output in the specified file or pipe the output to the specified shell command. ybsql writes to the file or command only when the query returns zero or more result rows, and not if the query fails or is not a SQL command that returns data. Entering \g by itself is equivalent to entering a semicolon. Using the \g command with a file or command name is similar to using the \o command but for one operation only.

For example:

premdb=# select * from match
premdb-# \g | grep "| 8-"
        8 | 1999-09-19 00:00:00 |   28 |   85 | 8-0     | 4-0
       16 | 2008-05-11 00:00:00 |   27 |   74 | 8-1     | 2-0
       18 | 2010-05-09 00:00:00 |   14 |   95 | 8-0     | 2-0
       20 | 2011-08-28 00:00:00 |   26 |   51 | 8-2     | 3-1
       21 | 2012-12-23 00:00:00 |   14 |   52 | 8-0     | 3-0
\gset [ prefix ]

Send the query input buffer to the server and store the query output into ybsql variables. The query must return exactly one row. Each column of the row is stored into a separate variable, which has the same name as the column. For example:

premdb=# select htid as var1, atid as var2 from team limit 1
premdb-# \gset
premdb=# \echo :var1 :var2
2 51

If you enter a prefix, it is prepended to the column names to create the variable names:

premdb=# select htid as var1, atid as var2 from team limit 1
premdb-# \gset bobr
premdb=# \echo :bobrvar1 :bobrvar2
2 51

If a column returns NULL, the variable is unset. If the query fails or does not return one row, no variables are changed.

\h [ command ]

Return a list of supported SQL commands, or a short description of a specific SQL command and a summary of its syntax. For example:

yellowbrick=# \h commit
Command:     COMMIT
Description: Commit the current transaction block.
Syntax:
COMMIT [ WORK | TRANSACTION ]
\H, \html

Display output in HTML format. If the format is already HTML, this command returns it to the default format, which is aligned text.

\i filename, \include filename

Read and execute the contents of the specified file. If the specified file is - (a hyphen), ybsql reads from standard input until an EOF or a \q command is found. To display the lines in the file as they are read, set the ECHO variable to all.

\ir filename, include_relative filename

Read and execute the contents of the specified file. When ybsql is invoked from a script, interpret file names relative to the directory in which the script is located, rather than the current working directory. When ybsql is executing in interactive mode, \ir works exactly the same as \i.

\l, \list

List databases. Use \l+ to return descriptive comments for each database.

See also Patterns in \d Commands.

\o filename, \o | command, \out filename, \out | command

Send query output (not including error messages) to a file or pipe the output into a shell command. For example:

premdb=# \o | grep 2010-03-06
premdb=# select * from match;
       18 | 2010-03-06 00:00:00 |    2 |   60 | 3-1     | 1-0
       18 | 2010-03-06 00:00:00 |   45 |   57 | 1-2     | 0-2
       18 | 2010-03-06 00:00:00 |   48 |   75 | 0-1     | 0-0
\p, \print

Print the current query buffer (if any) to standard output. For example:

premdb=# select * from match where seasonid=18 and htid=2 and atid=60;
       18 | 2010-03-06 00:00:00 |    2 |   60 | 3-1     | 1-0
premdb=# \p
select * from match where seasonid=18 and htid=2 and atid=60;
\password [ user ]

Change the named user's password (or the current user's password by default). See Connecting with a Secure Password.

\prompt [ text ] name

Prompt the user to enter text, which is assigned to the specified variable (name). Optionally, you can specify the prompt string (text). For example:

premdb=# \prompt YBUSER
Current ybsql user
premdb=# \echo :YBUSER
Current ybsql user

If the prompt string consists of multiple words, enclose it with single quotes.

\pset [ option [ value ] ]

Set options that affect the display of query results. For some options, if you do not specify a value, the option is toggled or unset. In other cases, the current setting is displayed. For details about each option, see ybsql Display Properties. Entering the \pset command without any arguments displays the current value of all display options.

\q, \quit

Exit the ybsql session.

\qecho text [ ... ]

Print the specified text to the current output channel, as defined by the \o command.

\r, \reset

Clear the query buffer.

\s [ filename ]

Display the ybsql command history or save the history to a file.

\set [ name [ value [ ... ] ] ]

Set a variable to a single value or to the concatenation of multiple values. For example:

premdb=# \set var1 2 3 4 5
premdb=# \echo :var1
2345

\set by itself returns a list of all current variable values. \set with a name but no value sets the variable to an empty value. Variable names are case-sensitive and may can contain letters, digits, and underscores. ybsql treats several variables with uppercase names as special; see ybsql Properties and Variables.

\setenv name [ value ]

Set an environment variable. For example:

\setenv YBUSER bobr

You can unset an environment variable by not entering a value. See ybsql Environment Variables.

\t

Do not print column names in query results or row counts at the end of the result set. If \t is already on, this command turns it off. This command is equivalent to \pset tuples_only.

\T table_options

Specify attributes to use within the table tag for HTML output (-H), such as cellpadding or bgcolor. This command is equivalent to \pset tableattr table_options.

\timing [ on | off ]

Display the duration, in milliseconds, of each SQL statement. If timing is already on, \timing turns it off.

\unset name

Unset (delete) the named ybsql variable. For example:

premdb=# \set YBPASSWORD bobr
premdb=# \echo :YBPASSWORD
bobr
premdb=# \unset YBPASSWORD
premdb=# \echo :YBPASSWORD
:YBPASSWORD
\w filename, \write filename, \w | command, \write | command

Write the current query buffer to the specified file or pipe it to the specified shell command.

\watch [ seconds ]

Execute the current query buffer every 2 seconds (by default) or at a specified interval. Execution repeats until it is interrupted or the query fails. For example:

premdb=# \watch 5
                     Watch every 5s	Tue Mar 20 18:14:21 2018

 teamid | htid | atid |  name  | nickname |  city  | stadium | capacity | avg_att 
--------+------+------+--------+----------+--------+---------+----------+---------
     48 |   49 |   98 | [NULL] | [NULL]   | [NULL] | [NULL]  |        0 |   0.000
     49 |   50 |   99 | [NULL] | [NULL]   | [NULL] | [NULL]  |        0 |   0.000
     50 |   51 |  100 | [NULL] | [NULL]   | [NULL] | [NULL]  |        0 |   0.000
(3 rows)

                     Watch every 5s	Tue Mar 20 18:14:26 2018

 teamid | htid | atid |  name  | nickname |  city  | stadium | capacity | avg_att 
--------+------+------+--------+----------+--------+---------+----------+---------
     48 |   49 |   98 | [NULL] | [NULL]   | [NULL] | [NULL]  |        0 |   0.000
     49 |   50 |   99 | [NULL] | [NULL]   | [NULL] | [NULL]  |        0 |   0.000
     50 |   51 |  100 | [NULL] | [NULL]   | [NULL] | [NULL]  |        0 |   0.000
(3 rows)
...
\x [ on | off | auto ]

Set or toggle expanded table formatting. This command is equivalent to \pset expanded.

\z

Alias for \dp.

\! [ command ]

Escape to a separate shell, or execute the specified shell command then return to the ybsql prompt.

\?

Shows help information for ybsql commands and options.

Patterns in \d Commands ​

The ybsql \d commands (\d, \du, \dv, and others) and \l accept patterns that filter the display of objects. By default, these commands display all objects that are visible to the current user in the current search path.

Note the following rules for using patterns:

  • The characters in a pattern are folded to lowercase. For example, \dt TEAM will display the table named team. To preserve any uppercase letters, use double quotes around all or part of a pattern. For example, \dt new"MATCH"stats will display the table named newMATCHstats.
  • To use an actual double quote character in a pattern, write it as a pair of double quotes within a double-quote sequence. For example, \dt "MATCH""STATS" will display the table named MATCH"STATS.
  • * matches any sequence of characters (including no characters). For example, \dt match* displays tables with names that begin with match.
  • ? matches any single character.
  • Within double quotes, *, ?, and . are all matched literally.
  • A dot (.) is interpreted as a separator for a schema name followed by an object name. For example, \dt pub*.*team displays tables that have names that end in team and schema names that start with pub.
  • Advanced users can specify patterns with regular-expression notations such as character classes (for example [0-9] to match any digit).

Parent topic:ybsql Reference