Meta-commands

• 1: Meta-commands quick reference
• 2: \connect
• 3: \d meta-commands
• 4: \edit
• 5: \i
• 6: \locale
• 7: \pset
• 8: \set
• 9: \timing

1 - Meta-commands quick reference

> \z *.*myseq*
     Access privileges for database "dbadmin"
 Grantee | Grantor | Privileges | Schema |  Name
---------+---------+------------+--------+--------
 dbadmin | dbadmin | SELECT*    | public | mySeq
 dbadmin | dbadmin | SELECT*    | public | mySeq2
(2 rows)

2 - \connect

Establishes a connection to database db, under the specified user user-name. The previous connection is closed. If you omit specifying a database name, Vertica connects to the current database. If you omit specifying a user name argument, Vertica assumes the current user.

Syntax

\c[connect] [db [user-name]]

Error handling

Errors that prevent execution include specifying an unknown user and denial of access to the specified database. Vertica handles errors differently, depending on whether this command is executed interactively in vsql, or in a script:

• VSQL handling: The current connection is maintained.

• Script: Processing immediately stops with an error. This is prevents scripts from acting on the wrong database.

3 - \d meta-commands

Vertica supports a number of \d commands, which return information on different categories of database objects. For a full list, see \d Reference below.

Syntax

Unless otherwise noted, \d commands generally conform to the following syntax:

\dCommand [ [schema.]pattern ]

Arguments

You can supply most \d commands with a string pattern argument, which filters the results that the command returns. The pattern can optionally be qualified by a schema name.

schema

Valid for most \d commands, restricts output to only database objects in schema. For example, the following \dp command obtains privileges information for all V_MONITOR tables that contain the string resource:

=> \dp V_MONITOR.*resource*
                Access privileges for database "dbadmin"
 Grantee | Grantor | Privileges |  Schema   |            Name
---------+---------+------------+-----------+----------------------------
 public  | dbadmin | SELECT     | v_monitor | resource_rejections
 public  | dbadmin | SELECT     | v_monitor | disk_resource_rejections
 public  | dbadmin | SELECT     | v_monitor | resource_usage
 public  | dbadmin | SELECT     | v_monitor | resource_acquisitions
 public  | dbadmin | SELECT     | v_monitor | resource_rejection_details
 public  | dbadmin | SELECT     | v_monitor | resource_pool_move
 public  | dbadmin | SELECT     | v_monitor | host_resources
 public  | dbadmin | SELECT     | v_monitor | node_resources
 public  | dbadmin | SELECT     | v_monitor | resource_queues
 public  | dbadmin | SELECT     | v_monitor | resource_pool_status
(10 rows)
  

pattern

Returns only the database objects that match the specified string. Pattern strings can include the following wildcards:

• * (asterisk): zero or more characters.

• ? (question mark): any single character.

For example, the following \dt command returns tables that start with the string store:

=> \dt store*
                     List of tables
 Schema |       Name        | Kind  |  Owner  | Comment
--------+-------------------+-------+---------+---------
 public | store_orders      | table | dbadmin |
 public | store_orders_2018 | table | dbadmin |
 public | store_overseas    | table | dbadmin |
 store  | store_dimension   | table | dbadmin |
 store  | store_orders_fact | table | dbadmin |
 store  | store_sales_fact  | table | dbadmin |
(6 rows)
  

\d reference

\d

Unqualified by a pattern argument, returns all tables with their schema names, owners, and comments. If qualified by a pattern argument, \d returns all matching tables and all columns in each table, with details about each column, such as data type, size, and default value.

\df

Returns all function names, the function return data type, and the function argument data type. Also returns the procedure names and arguments for all procedures that are available to the user.

\dj

Returns all projections showing the schema, projection name, owner, and node. The returned rows include superprojections, live aggregate projections, Top-K projections, and projections with expressions.

\dn

Returns the schema names and schema owner.

\dp

Returns a summary of privileges on all objects in system table V_CATALOG.GRANTS: grantee, grantor, privileges, schema, and object name (equivalent to \z).

\dS

Unqualified by a pattern argument, returns all V_CATALOG and V_MONITOR system tables. To obtain system tables for just one schema, qualify the command with the schema name, as follows:

\dS { V_CATALOG | V_MONITOR }.*

\ds

Returns sequences and their parameters.

\dT

Returns all data types that Vertica supports.

Note

\dT returns no results if qualified with a pattern argument.

\dt

Unqualified by a pattern argument, returns the same information as an unqualified \d command. If qualified by a pattern argument, \dt returns matching tables with the same level of detail as an unqualified \dt command.

\dtv

Returns tables and views.

\du

Returns database users and whether they are superusers.

\dv

Unqualified by a pattern argument, returns all views with their schema names, owners, and comments. If qualified by a pattern argument, \dv returns all matching views and the columns in each view, with each column's data type and size.

4 - \edit

Edits the query buffer (or specified file) with an external editor. When the editor exits, its content is copied back to the query buffer. If no argument is given, the current query buffer is copied to a temporary file which is then edited in the same fashion.

The new query buffer is then re-parsed according to the normal rules of vsql, where the whole buffer up to the first semicolon is treated as a single line. (Thus you cannot make scripts this way. Use \i for that.) If there is no semicolon, vsql waits for one to be entered (it does not execute the query buffer).

Syntax

\e[dit] [ file ]

5 - \i

Reads and executes input from the specified file.

Syntax

\i filename

Examples

The Vertica vsql client on Linux supports backquote (backtick) expansion. For example:

1. Set an environment variable to a path that contains scripts you want to run:

   $ export MYSCRIPTS=/home/dbadmin/testscripts
   

2. Issue the vsql command.

   $ vsql
   

3. Use backquote expansion to include the path for running an existing script—for example, sample.sql.

   => \i `echo $MYSCRIPTS/sample.sql`
   

6 - \locale

Displays or sets the locale setting for the current session.

Syntax

\locale [locale-identifier]

Arguments

locale-identifier

Specifies the ICU locale identifier to use, by default set to:

en_US@collation=binary

If set to an empty string, Vertica sets locale to en_US_POSIX.

If you omit this argument, \locale returns the current locale setting.

For details on identifier options, see About locale. For a complete list of locale identifiers, see the ICU Project.

Examples

View the current locale setting:

=> \locale
en_US@collation=binary

Change the default locale for this session:

=> \locale en_GBINFO:
INFO 2567:  Canonical locale: 'en_GBINFO:'
Standard collation: 'LEN'
English (GBINFO:)

Notes

The server locale settings impact only the collation behavior for server-side query processing. The client application is responsible for ensuring that the correct locale is set in order to display the characters correctly. Below are the best practices recommended by Vertica to ensure predictable results:

• The locale setting in the terminal emulator for vsql (POSIX) should be set to be equivalent to session locale setting on server side (ICU) so data is collated correctly on the server and displayed correctly on the client.

• The vsql locale should be set using the POSIX LANG environment variable in terminal emulator. Refer to the documentation of your terminal emulator for how to set locale.

• Server session locale should be set using the set as described in Specify the default locale for the database.

• All input data for vsql should be in UTF-8 and all output data is encoded in UTF-8.

• Non UTF-8 encodings and associated locale values are not supported.

7 - \pset

Sets options that control how Vertica formats query result output.

Syntax

\pset output-option

Output options

format format-option

Sets output format, where format-option is one of the following:

• u[naligned] writes all column data of each row on a single line, where each field is separated only by the current separator character. Use this output for use as input to other programs—for example, comma-delimited fields for CSV input.

• a[ligned] (default): Renders column-aligned output.

• h[tml]: Renders output in HTML markup as a table.

• l[atex]: Renders output in LaTex markup.

border int

Valid only if output format is set to html, specifies the table border, where int specifies the border type.

expanded

Toggles between regular and expanded format. When expanded format is enabled, all output has two columns with the column name on the left and the data on the right. This mode is especially useful for wide tables.

fieldsep 'arg'

Valid only if output format is set to unaligned, specifies the field separator, by default | (vertical bar).

For example, to specify tab as the field separator:

\pset fieldsep '\t'

footer

Toggles display of the default footer:

(int rows)

null 'string'

Specifies to represent column null values as string. By default, Vertica renders null values as an empty field, which might be mistaken as an empty string.

For example:

\pset null '(null)'

pager [always]

Toggles use of a pager for query and vsql help output. If the environment variable PAGER is set, the output is piped to the specified program. Otherwise a platform-dependent default (such as more) is used.

When the pager is off, the pager is not used. When the pager is on, the pager is used only when appropriate; that is, the output is to a terminal and does not fit on the screen. (vsql does not do a perfect job of estimating when to use the pager.)

If qualified with the argument always, the pager is always used.

recordsep 'char'

Valid only if output format is set to unaligned, specifies the character used to delimit table records (tuples), by default a newline character.

tableattr html-attribute[...]

Specifies attributes to be placed inside the HTML table tag—for example, cellpadding or bgcolor.

title ['title-str']

Sets a title that precedes query result output, to title-str. HTML output renders this as follows:

<caption>title-str</caption>

To remove the title, reissue the command omit the title-str argument.

trailingrecordsep

Toggles on or off the trailing record separator to use in unaligned output mode.

t[uples_only]

Toggles between tuples only and full display. Full display might show extra information such as column headers, titles, and various footers. In tuples only mode, only actual table data is shown.

Shortcuts

The following \pset commands have short-cuts:

\pset expanded

\x

\pset fieldsep 'arg'

\f

\pset format aligned

\a

\pset format html

\H

\pset tableattr html-attribute[...]

\T html-attribute[...]

\pset title title-str

\C ['title-str']

\pset tuples_only

\t

Examples

See Output formatting examples.

8 - \set

Sets an internal variable to one or more values. If multiple values are specified, they are concantenated. An unqualified \set command lists all internal variables.

To unset a variable, use vsql meta-command\unset.

Syntax

\set [var [value]...]

Arguments

var

The name of an internal variable to set. Valid variable names are case sensitive and can contain characters, digits, and underscores. vsql treats several variables as special, which are described in Variables.

value

A value to set in variable var. If no value is specified, the variable is set to no value.

If set to an empty string, the variable is set to no value. If you omit this argument, \set returns all internal variables.

If no arguments are supplied, \set returns all internal variables. For example:

=> \set
VERSION = 'vsql'
AUTOCOMMIT = 'off'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'dbadmin'
USER = 'dbadmin'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
HISTSIZE = '500'

9 - \timing

If set to on, returns how long (in milliseconds) each SQL statement runs. Results include:

• Length of time required to fetch the first block of rows

• Total time until the last block is formatted.

Unqualified, \timing toggles timing on and off. You can explicitly turn timing on and off by qualifying the command with options ON and OFF, respectively.

Syntax

\timing [ON | OFF]

Examples

The following unqualified \timing commands toggle timing on and off:

=> \timing
Timing is on
=> \timing
Timing is off

The following example shows a SQL command with timing on:

=> \timing
Timing is on.
=> SELECT user_name, ssl_state, authentication_method, client_authentication_name,
     client_type FROM sessions WHERE session_id=(SELECT session_id FROM current_session);
 user_name | ssl_state | authentication_method | client_authentication_name | client_type
-----------+-----------+-----------------------+----------------------------+-------------
 dbadmin   | None      | ImpTrust              | default: Implicit Trust    | vsql
(1 row)

Time: First fetch (1 row): 73.684 ms. All rows formatted: 73.770 ms