Updated on 2025-04-14 GMT+08:00

Meta-Command Reference

This section describes meta-commands provided by gsql after GaussDB CLI tool is used to connect to a database. After connecting to the database, you can run the \? command to display the help information about all available meta-commands.

Precautions

  • The format of the gsql meta-command is a backslash (\) followed by a command verb, and then a parameter. The parameters are separated from the command verb and from each other by any number of whitespace characters.
  • To include whitespace characters into an argument, you must quote them with a single straight quotation mark. To include a single straight quotation mark into such an argument, precede it by a backslash. Anything contained in single quotation marks is furthermore subject to C-like substitutions for \n (new line), \t (tab), \b (backspace), \r (carriage return), \f (form feed), \digits (octal), and \xdigits (hexadecimal).
  • Within a parameter, text enclosed in double quotation marks ("") is taken as a command line input to the shell. The output of the command (with any trailing newline removed) is taken as the argument value.
  • If an unquoted argument begins with a colon (:), the argument is taken as a gsql variable and the value of the variable is used as the argument value instead.
  • Some commands take an SQL identifier (such as a table name) as a parameter. These parameters follow the SQL syntax rules: Unquoted letters are forced to lowercase, while double quotation marks ("") protect letters from case conversion and allow incorporation of whitespace into the identifier. Within double quotation marks, paired double quotation marks reduce to a single double quotation mark in the result name. For example, FOO"BAR"BAZ is interpreted as fooBARbaz, and "Aweird""name" becomes Aweird"name.
  • The analysis of parameters stops at another unquoted backslash (\), which is considered the beginning of a new meta-command. Parameters end with two backslashes (\\) and then SQL statements can be analyzed, if any. In this way, SQL and gsql commands can be used together in a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.
  • M-compatible databases do not support the \h meta-command.

Command Format and Parameters

For details about meta-commands, see Table 1, Table 2, Table 3, Table 4, Table 6, Table 8, Table 9, Table 10, Table 12, and Table 13.

FILE mentioned in the following commands indicates a file path. This path can be an absolute path such as /home/gauss/file.txt, or a relative path such as file.txt. By default, a file.txt is created in the path where the user runs gsql commands.

Table 1 Common meta-commands

Parameter

Description

Value Range

\copyright

Displays GaussDB version and copyright information.

-

\g [FILE] or ;

Performs a query operation and sends the result to a file or pipe.

-

\h(\help) [NAME]

Provides syntax help on the specified SQL statement.

If the name is not specified, then gsql will list all the commands for which syntax help is available. If the name is an asterisk (*), syntax help on all SQL statements is displayed.

\parallel [on [num]|off]

Controls the parallel execution function.

  • on: The switch is enabled and the maximum number of concurrently executed tasks is num. The default value of num is 1024.
  • off: This switch is disabled.
NOTE:
  • Parallel execution is not allowed in a running transaction and a transaction is not allowed to be started during parallel execution.
  • Parallel execution of \d meta-commands is not allowed.
  • If SELECT statements are run concurrently, the return results may be displayed randomly but acceptable; however, if a core dump or process response failure occurs, it is not acceptable.
  • It is not recommended that you run SET statements in concurrent tasks because they may cause unexpected results.
  • Creation of a temporary table is not supported. If temporary tables are required, create them before parallel execution is enabled, and use them only in the parallel execution. Temporary tables cannot be created in parallel execution.
  • When \parallel is executed, num independent gsql processes can be connected to the database server.
  • The total duration of all \parallel tasks cannot exceed session_timeout. Otherwise, the connection may fail during concurrent execution.
  • One or more commands following \parallel on will be executed only after \parallel off is executed. Therefore, \parallel on must be followed by \parallel off. Otherwise, the commands following \parallel on cannot be executed.
  • The maximum number of connections allowed by the server is determined based on max_connection and the number of current connections. Set num based on the allowed number of connections.

-

\q

Exits the gsql program. This command is executed in a script file only when the script terminates.

-

delimiter

Sets a delimiter for the client. When a delimiter is set, the gsql client sends the SQL statements to the server for execution immediately after identifying the delimiter. However, the server still considers the semicolon (;) as the SQL statement delimiter and processes the SQL statements accordingly.

The default delimiter between SQL statements is a semicolon (;).

The terminator is set at the session level. In case of database switching, the default delimiter semicolon (;) is used.

NOTE:

The delimiter is supported only when sql_compatibility is set to 'B'.

  • Currently, delimiters cannot be set freely. The terminator can be a combination of uppercase and lowercase letters or a combination of special characters (~ ! @ # ^ & ` ? + - * / % < > =). The common delimiter is //.
  • The combination of special characters should be unambiguous. Ambiguous combinations, such as comment characters \* and -- and combinations ending with a plus sign (+) or minus sign (-), cannot be used for delimiter naming.
  • The delimiter length ranges from 0 to 15 characters.
  • To use other combinations, you can add quotation marks (for example, delimiter "adbc $$"). The quotation marks are required in statements, for example, select 1"adbc $$".
Table 2 Query buffer meta-commands

Parameter

Description

\e [FILE] [LINE]

Uses an external editor to edit the query buffer or file.

\ef [FUNCNAME [LINE]]

Edits the function definition using an external editor. If LINE is specified, the cursor will point to the specified line of the function body.

\p

Prints the current query buffer to the standard output.

\r

Resets or clears the query buffer.

\w FILE

Outputs the current query buffer to a file.

Table 3 Input/Output commands

Parameter

Description

\copy { table [ ( column_list ) ] | ( query ) } { from | to } { filename | stdin | stdout | pstdin | pstdout } [copy parameter][parallel integer]

After logging in to the database on any gsql client, you can import and export data. This is an operation of running the SQL COPY command, but not the server that reads or writes data to a specified file. Instead, data is transferred between the server and the local file system. In this way, local user permissions instead of server permissions are required for file access, and the user permissions do not need to be initialized.

NOTE:
  • \COPY only applies to small-batch data import with uniform formats. GDS or COPY is preferred for data import.
  • \COPY specifies the number of clients to import data to implement parallel import of data files. Currently, the value range is [1,8].
  • Parallel import using \COPY is not supported for temporary tables, binary files, data encrypted using AES-128, COPY option containing EOL, or inside a transaction. In these cases, even if the parallel parameter is specified, a non-parallel process is performed.
  • Both the TEXT and CSV formats of \COPY support the header function.
  • The LOAD function is used by gs_loader to call COPY after syntax conversion. It is not an active function call.
  • The LOAD_DISCARD function is used by gs_loader to discard file path after parsing. It is not an active function call.
  • \COPY supports all COPY commands, which are represented by copy parameter in the parameter list. For details about all COPY commands, see "SQL Reference > SQL Syntax > C > COPY" in Developer Guide.

\echo [STRING]

Writes character strings to the standard output.

prompt [STRING]

Writes character strings to the standard output, which is equivalent to \echo.

\i FILE

Reads content from FILE and uses them as the input for a query.

\i+ FILE KEY

Runs commands in an encrypted file.

\ir FILE

Similar to \i, but resolves relative path names differently.

\ir+ FILE KEY

Similar to \i+, but resolves relative path names differently.

\o [FILE]

Saves all query results to a file.

\qecho [STRING]

Writes character strings to the query output flow.

Table 4 Information display meta-commands

Parameter

Description

Value Range

Example

\d[S+]

Lists all tables, views, and sequences of all schemas in search_path. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed.

-

List all tables, views, and sequences of all schemas in search_path.

1
gaussdb=# \d

\d[S+] NAME

Lists the structure of specified tables, views, and indexes. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed.

-

List the structure of table a.

1
gaussdb=# \dtable+ a

\d+ [PATTERN]

Lists all tables, views, and indexes. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only tables, views, and indexes whose names match PATTERN are displayed.

-

Lists all tables, views, and indexes whose names start with f.

1
gaussdb=# \d+ f*

\da[S] [PATTERN]

Lists all available aggregate functions, together with the data type they perform operations on and the return value types. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only aggregate functions whose names match PATTERN are displayed.

-

Lists all available aggregate functions whose names start with f, together with their return value types and the data types.

1
gaussdb=# \da f*

\db[+] [PATTERN]

Lists all available tablespaces. If PATTERN is specified, only tablespaces whose names match PATTERN are displayed.

-

Lists all available tablespaces whose names start with p.

1
gaussdb=# \db p*

\dc[S+] [PATTERN]

Lists all available conversions between character-set encodings. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only conversions whose names match PATTERN are displayed.

-

Lists all available conversions between character-set encodings.

1
gaussdb=# \dc *

\dC[+] [PATTERN]

Lists all available type conversions. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only conversions whose names match PATTERN are displayed.

PATTERN must be the actual type name and cannot be an alias.

Lists all type conversions whose patten names start with c.

1
gaussdb=# \dC c*

\dd[S] [PATTERN]

Displays all visible objects. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only matched objects are displayed. The objects include aggregations, functions, operators, types, relations (tables, views, indexes, sequences, and large objects), and rules.

-

Lists all visible objects.

1
gaussdb=# \dd

\ddp [PATTERN]

Lists all default permissions. If PATTERN is specified, only permissions whose names match PATTERN are displayed.

-

Lists all default permissions.

1
gaussdb=# \ddp

\dD[S+] [PATTERN]

Lists all available domains. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only domains whose names match PATTERN are displayed.

-

Lists all available domains.

1
gaussdb=# \dD

\det[+] [PATTERN]

Lists all external tables. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only tables whose names match PATTERN are displayed.

-

Lists all external tables.

1
gaussdb=# \det

\des[+] [PATTERN]

Lists all external servers. If PATTERN is specified, only servers whose names match PATTERN are displayed.

-

Lists all external servers.

1
gaussdb=# \des

\deu[+] [PATTERN]

Lists all user mappings. If PATTERN is specified, only information whose name matches PATTERN is displayed.

-

Lists all user mappings.

1
gaussdb=# \deu

\dew[+] [PATTERN]

Lists all encapsulated external data. If PATTERN is specified, only data whose name matches PATTERN is displayed.

-

Lists all encapsulated external data.

1
gaussdb=# \dew

\df[antw][S+] [PATTERN]

Lists all available functions, together with their parameters and return types. a indicates an aggregate function, n indicates a common function, t indicates a trigger, and w indicates a window function. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only functions whose names match PATTERN are displayed.

-

Lists all available functions, together with their parameters and return types.

1
gaussdb=# \df

\dF[+] [PATTERN]

Lists all text search configuration information. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only configurations whose names match PATTERN are displayed.

-

Lists all text search configuration information.

1
gaussdb=# \dF+

\dFd[+] [PATTERN]

Lists all text search dictionaries. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only dictionaries whose names match PATTERN are displayed.

-

Lists all text search dictionaries.

1
gaussdb=# \dFd

\dFp[+] [PATTERN]

Lists all text search analyzers. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only analyzers whose names match PATTERN are displayed.

-

Lists all text search analyzers.

1
gaussdb=# \dFp

\dFt[+] [PATTERN]

Lists all text search templates. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only templates whose names match PATTERN are displayed.

-

Lists all text search templates.

1
gaussdb=# \dFt

\dg[+] [PATTERN]

Lists all database roles. If PATTERN is specified, only roles whose names match PATTERN are displayed.

NOTE:

Since the concepts of "users" and "groups" have been unified into "roles", this command is now equivalent to \du. Both commands are retained to ensure compatibility with earlier versions.

-

Lists all database roles named j?e (the question mark (?) indicates any character).

1
gaussdb=# \dg j?e

\dl

Alias of \lo_list, which shows a list of large objects.

-

List all large objects.

1
gaussdb=# \dl

\dL[S+] [PATTERN]

Lists all available program languages. If PATTERN is specified, only languages whose names match PATTERN are displayed.

-

Lists all available program languages.

1
gaussdb=# \dL

\dm[S+] [PATTERN]

Lists materialized views. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only materialized views whose names match PATTERN are displayed.

-

Lists materialized views.

1
gaussdb=# \dm

\dn[S+] [PATTERN]

Lists all schemas (namespace). If + is added to the command, the permission and description of each schema are listed. If PATTERN is specified, only schemas whose names match the pattern are shown. By default, only schemas you created are displayed.

-

Lists information about all schemas whose names start with d.

1
gaussdb=# \dn+ d*

\do[S] [PATTERN]

Lists available operators with their operand and return types. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only operators whose names match PATTERN are displayed. By default, only the operators created by the user are listed.

-

Lists available operators with their operand and return types.

1
gaussdb=# \do

\dO[S+] [PATTERN]

Lists collation rules. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only rules whose names match PATTERN are displayed. By default, only rules you created are displayed.

-

Lists collation rules.

1
gaussdb=# \dO

\dp [PATTERN]

Lists tables, views, and related permissions. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only tables and views whose names match the pattern are shown.

-

Lists tables, views, and related permissions.

1
gaussdb=# \dp

The following result about \dp is displayed:

rolename=xxxx/yyyy  -- Assigns permissions to a role.
=xxxx/yyyy -- Assigns permissions to public.

xxxx indicates the assigned permissions, and yyyy indicates the roles with the assigned permissions. For details about permission descriptions, see Table 5.

\drds [PATTERN1 [PATTERN2]]

Lists all parameters that have been modified. These settings can be for roles, for databases, or for both. PATTERN1 and PATTERN2 indicate a role pattern and a database pattern, respectively. If PATTERN is specified, only collations rules whose names match PATTERN are displayed. If the default value is used or * is specified, all settings are listed.

-

List all the modified configuration parameters of the database.

1
gaussdb=# \drds * dbname

\dT[S+] [PATTERN]

Lists all data types. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. If PATTERN is specified, only types whose names match PATTERN are displayed.

-

Lists all data types.

1
gaussdb=# \dT

\du[+] [PATTERN]

Lists all database roles. If PATTERN is specified, only roles whose names match PATTERN are displayed.

NOTE:

Since the concepts of "users" and "groups" have been unified into "roles", this command is now equivalent to \dg. Both commands are retained to ensure compatibility with earlier versions.

-

Lists all database roles.

1
gaussdb=# \du

\dE[S+] [PATTERN]

\di[S+] [PATTERN]

\ds[S+] [PATTERN]

\dt[S+] [PATTERN]

\dv[S+] [PATTERN]

In this group of commands, the letters E, i, s, t, and v stand for foreign table, index, sequence, table, and view, respectively. You can specify any or a combination of these letters sequenced in any order to obtain an object list. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed. For example, \dit lists all indexes and tables. If a plus sign (+) is added to the end of a command name, the physical size and related description of each object are also listed. If PATTERN is specified, only objects whose names match PATTERN are displayed. By default, only objects you created are displayed. You can specify PATTERN or S to view other system objects.

-

Lists all indexes and views.

1
gaussdb=# \div

\dx[+] [PATTERN]

Lists installed extensions. If PATTERN is specified, only extensions whose names match PATTERN are displayed.

-

Lists installed extensions.

1
gaussdb=# \dx

\l[+]

Lists the names, owners, character set encodings, and permissions of all the databases in the server.

-

List the names, owners, character set encodings, and permissions of all the databases in the server.

1
gaussdb=# \l

\sf[+] FUNCNAME

Displays the definition of a function.

NOTE:

If the function name contains parentheses, enclose the function name with double quotation marks and add the parameter type list following the double quotation marks. Also enclose the list with parentheses.

If a function with the same name exists, the definitions of multiple functions are returned.

-

Assume a function function_a and a function func()name. This parameter will be as follows:

1
2
3
gaussdb=# \sf function_a
gaussdb=# \sf 
"func()name"(argtype1, argtype2)

\z [PATTERN]

Lists all tables, views, and sequences in the database and their access permissions. If a pattern is given, it is a regular expression, and only matched tables, views, and sequences are shown.

-

Lists all tables, views, and sequences in the database and their access permissions.

1
gaussdb=# \z
  • In Table 4, S indicates that the system object is displayed and + indicates that additional object information is displayed.
  • PATTERN specifies the name of the object to be displayed. Pay attention to the following points about PATTERN:
    • In the simplest case, PATTERN is the exact name of the object. Characters in PATTERN are usually converted to lowercase (as in SQL names), for example, \dt FOO will display a table named foo. As in SQL names, placing double quotation marks (") around a pattern prevents them being folded to lower case. If you need to include a double quotation mark (") in a pattern, write it as a pair of double quotation marks ("") within a double-quote sequence, which is in accordance with the rules for SQL quoted identifiers. For example, \dt "FOO""BAR" will be displayed as a table named FOO"BAR instead of foo"bar. You cannot put double quotation marks around just part of a pattern, which is different from the normal rules for SQL names. For example, \dt FOO"FOO"BAR will be displayed as a table named fooFOObar if just part of a pattern is quoted.
    • Whenever the PATTERN parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path, which is equivalent to using an asterisk (*) as the pattern. An object is regarded to be visible if it can be referenced by name without explicit schema qualification. To see all objects in the database regardless of their visibility, use a dot within double quotation marks (*.*) as the pattern.
    • Within a pattern, the asterisk (*) matches any sequence of characters (including no characters) and a question mark (?) matches any single character. This notation is comparable to Unix shell file name patterns. For example, \dt int* displays tables whose names start with int. But within double quotation marks, the asterisk (*) and the question mark (?) lose these special meanings and are just matched literally.
    • A pattern that contains a dot (.) is interpreted as a schema name pattern followed by an object name pattern. For example, \dt foo*.*bar* displays all tables (whose names include bar) in schemas starting with foo. If no dot appears, then the pattern matches only visible objects in the current schema search path. Likewise, the dot within double quotation marks loses its special meaning and becomes an ordinary character.
    • Senior users can use regular-expression notations, such as character classes. For example [0-9] can be used to match any digit. All regular-expression special characters work as specified in POSIX. The following characters are excluded:
      • A dot (.) is used as a delimiter.
      • An asterisk (*) is translated into an asterisk prefixed with a dot (.*), which is a regular-expression marking.
      • A question mark (?) is translated into a dot (.).
      • A dollar sign ($) is matched literally.
    • You can write ?, (R+|), (R|), and R to the following pattern characters: ., R*, and R?. The dollar sign ($) does not need to be used as a regular expression character because PATTERN must match the entire name instead of being interpreted as a regular expression (in other words, $ is automatically appended to PATTERN). If you do not expect a pattern to be anchored, write an asterisk (*) at its beginning or end. All regular-expression special characters within double quotation marks lose their special meanings and are matched literally. Regular-expression special characters in operator name patterns (such as the \do parameter) are also matched literally.
Table 5 Description of permissions

Parameter

Description

r

SELECT: allows users to read data from specified tables and views.

w

UPDATE: allows users to update columns for specified tables.

a

INSERT: allows users to insert data to specified tables.

d

DELETE: allows users to delete data from specified tables.

D

TRUNCATE: allows users to delete all data from specified tables.

x

REFERENCES: allows users to create FOREIGN KEY constraints.

t

TRIGGER: allows users to create a trigger on specified tables.

X

EXECUTE: allows users to use specified functions and the operators that are realized by the functions.

U

USAGE:

  • For procedural languages, allows users to specify a procedural language when creating a function.
  • For schemas, allows users to access objects included in specified schemas.
  • For sequences, allows users to use the nextval function.

C

CREATE:

  • For databases, allows new schemas to be created within the database.
  • For schemas, allows users to create objects in a schema.
  • For tablespaces, allows users to create tables in a tablespace and set the tablespace to default one when creating databases and schemas.

c

CONNECT: allows users to connect to specified databases.

T

TEMPORARY: allows users to create temporary tables.

A

ALTER: allows users to modify the attributes of a specified object.

P

DROP: allows users to delete specified objects.

m

COMMENT: allows users to define or modify comments of a specified object.

i

INDEX: allows users to create indexes on specified tables.

v

VACUUM: allows users to perform ANALYZE and VACUUM operations on specified tables.

*

Grants the preceding permissions.

Table 6 Formatting meta-commands

Parameter

Description

Value Range

\a

Switches between aligned and unaligned table output formats.

-

\C [STRING]

Sets the title for the table output, or cancels the title (if there is no parameter).

Any character string or null.

\f [STRING]

Specifies the character or string used to separate fields in unaligned output mode.

Any character string.

\H

Switches between standard output and HTML table output.

-

\pset NAME [VALUE]

Sets options affecting the output of query result tables.

For details about the value of NAME, see Table 7.

\t [on|off]

Switches the table output format.

  • on: Only data rows are displayed. Column titles and row counts are not displayed.
  • off: The entire table, including the column titles and row counts, is displayed.

\T [STRING]

Specifies attributes to be placed within the table tag in HTML output format. If this parameter is left empty, no setting is performed.

-

\x [on|off|auto]

Switches the display mode of the extended rows.

  • on: The extended display mode is enabled. Each field is displayed in a separate row.
  • off: The extended display mode is disabled and the standard table display mode is used.
  • auto: The extended display mode is used depending on the width of the query result.
Table 7 Adjustable printing options

Option

Description

Value Range

border

Controls the border style of the table output.

  • The value is an integer greater than 0 in HTML format.
  • The value range in other formats is as follows:
    • 0: no border. No border is displayed when the table is output.
    • 1: inner borders dividing columns, without outside borders.
    • 2: all borders, including the outside and inner borders.

expanded (or x)

Switches between regular and expanded formats.

  • on: The extended display mode is enabled. Each field is displayed in a separate row.
  • off: The extended display mode is disabled and the standard table display mode is used. The standard format is effective only in the aligned or wrapped mode.
  • auto: The extended display mode is used depending on the width of the query result.

fieldsep

Specifies the delimiter between columns in unaligned output mode.

Any character string. Common values include ',', '\t', and '|'. If this option is not set, spaces are used as delimiters by default.

fieldsep_zero

Sets the delimiter between columns in unaligned output mode to ASCII NUL.

-

footer

Sets the footer of the query result.

  • on: The footer information is displayed.
  • off: The footer information is not displayed.

format

Specifies the output format of the query result.

  • unaligned (u): Columns are displayed in unaligned mode and are separated by the current delimiter.
  • aligned (a): The table is displayed in aligned mode.
  • wrapped (w): The table is displayed similar to the aligned mode but automatically wraps any excessively long value.
  • html (h): The table is displayed in HTML table format.
  • latex (l): The table is displayed in LaTeX format.
  • troff-ms (t): The table is displayed in the troff ms macro format.

null

Specifies the display mode of NULL values.

Any character string. If this option is not set, whitespace characters are used to replace NULL values by default.

numericlocale

Determines whether to use the locale of the current system when numbers are output.

  • on: The locale settings of the current system are used for formatting when numbers are output. For example, the number 1,000.00 is displayed as 1,000.00 in English and 1.000,00 in German.
  • off: The standard numeric format, such as 1000.00, is used for number output.

pager

Controls the use of a pager for query and gsql help outputs. If the PAGER environment variable is set, the output is redirected to the specified program. Otherwise, the platform-dependent default value is used.

  • on: The pager is used for terminal output that does not fit the screen.
  • off: The pager is not used.
  • always: The pager is used for all terminal output regardless of whether it fits the screen.

recordsep

Sets the record delimiter in unaligned output mode.

Any character string. Common values include the newline characters (\n), carriage return character (\r), and carriage return line feed character (\r\n). If this option is not set, the newline character (\n) is used as the record delimiter by default.

recordsep_zero

Sets the record delimiter between columns in unaligned output mode to ASCII NUL.

-

tableattr (or T)

Declares the attributes to be placed in the HTML table tag in the HTML output format. Note: If the \pset border command has been executed, you do not need to set the border.

For example, bgcolor=#FFFFFF. If no value is given, the table attributes are not set.

title

Specifies the title of the query result.

Any character string. If this option is not set, no title is displayed in the query result.

tuples_only (or t)

Enables or disables the tuples-only mode. Full display may show extra information, such as column headers, titles, and various footers. In tuples_only mode, only the table data is shown.

-

feedback

Specifies whether to output the number of rows in the query result.

-

Table 8 Connection meta-commands

Parameter

Description

Value Range

\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]

Connects to a new database. If a database name contains more than 63 bytes, only the first 63 bytes are valid and are used for connection. However, the database name displayed in the command line of gsql is still the name before the truncation.

NOTE:

If the database login user is changed during reconnection, you need to enter the password of the new user. The maximum length of the password is 999 bytes, which is restricted by the maximum value of the GUC parameter password_max_length.

-

\encoding [ENCODING]

Sets the client character set encoding.

Without an argument, this command shows the current encoding.

\conninfo

Prints information about the current connected database.

-

Table 9 OS meta-commands

Parameter

Description

Value Range

\cd [DIR]

Changes the current working directory.

An absolute path or relative path that meets the OS path naming convention.

\setenv NAME [VALUE]

Sets the NAME environment variable to VALUE. If VALUE is not provided, do not set the environment variable.

-

\timing [on|off]

Toggles a display of how long each SQL statement takes, in milliseconds (exclude the time of screen displaying).

  • on: The display is enabled.
  • off: The display is disabled.

\! [COMMAND]

Runs OS commands. If no command follows \!, running it will open an independent shell UI for user interaction.

-

Table 10 Variable meta-commands

Parameter

Description

\prompt [TEXT] NAME

Prompts the user to enter a value and stores it in a gsql variable. TEXT indicates the text that prompts the user to enter, and NAME indicates the gsql variable that stores the input value. Example:

gaussdb=#  \prompt 'Enter area name: ' area
Enter area name: Asia
gaussdb=#  SELECT * FROM HR.areaS WHERE area_name = :'area';
area_id | area_name
---------+-----------
3 | Asia
(1 row)

\set [NAME [VALUE]]

Sets the internal variable NAME to VALUE. If more than one value is given, the variable value is the concatenation result of all values. If no second argument is given, the variable is just set with no value.

Some common variables are processed differently in gsql and they are combinations of uppercase letters, numbers and underscores.

Table 11 describes a list of variables that are processed in a way different from other variables.

\unset NAME

Deletes the variable name of gsql.

Table 11 Common \set commands

Command

Description

Value Range

\set VERBOSITY value

Controls the redundant lines in error reports.

  • default: Critical and major error texts and text locations, error details, and error messages (possibly involving multiple lines) are all returned.
  • verbose: All error information is returned.
  • terse: Only critical and major error texts and text locations are returned (which is generally suitable for single-line error information).

\set ON_ERROR_STOP value

If this variable is set, the script execution stops immediately. If this script is called from another script, that script will be stopped immediately as well. If the primary script is called using the -f option rather than from one gsql session, gsql will return error code 3, indicating the difference between the current error and critical errors. (The error code for critical errors is 1.)

  • on: The execution stops if an error occurs. In interactive mode, gsql returns the output of executed commands immediately.
  • off: An error, if occurring during the execution, is ignored, and the execution continues.

\set AUTOCOMMIT [on|off]

Controls the auto commit behavior of the current gsql connection. By default, the gsql connection is automatically committed, and each individual statement is implicitly committed. If auto commit is disabled for performance or other purposes, you need to explicitly run the COMMIT command to ensure that transactions are committed. For example, execute the COMMIT statement to explicitly commit transactions after a specified service SQL statement is executed. Particularly, ensure that all transactions are committed before the gsql client exits.

NOTE:

The auto commit function is enabled in gsql by default. If you disable it, all the statements executed later will be packaged in implicit transactions, and you cannot execute statements that cannot be executed within transactions.

  • on: Auto commit is enabled.
  • off: Auto commit is disabled.
Table 12 Large object meta-commands

Parameter

Description

\lo_list

Displays a list of all GaussDB large objects stored in the database, along with the comments provided for the large objects.

Table 13 Fully-encrypted meta-commands

Parameter

Description

\send_token

Sends keys to the server for caching. This function is used only when the memory decryption emergency channel is enabled. This is a fully-encrypted function.

\st

Sends keys to the server for caching. This function is used only when the memory decryption emergency channel is enabled. This is a fully-encrypted function.

\clear_token

Destroys the keys cached on the server. This function is used only when the memory decryption emergency channel is enabled. This is a fully-encrypted function.

\ct

Destroys the keys cached on the server. This function is used only when the memory decryption emergency channel is enabled. This is a fully-encrypted function.

\key_info KEY_INFO

In the fully-encrypted database features, this parameter is used to set the parameters for accessing the external key manager.

Currently, fully-encrypted databases are not M-compatible.

Examples

Example 1

Obtain the help information.

-- Connect to a database. (Replace the database name and port number as required.)
gsql -d postgres -p 8000
gsql ((GaussDB Kernel XXX.X.XXX build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

-- View all SQL statements supported by GaussDB.
gaussdb=#  \h
Available help:
  ABORT                            
  ALTER AGGREGATE
  ALTER APP WORKLOAD GROUP                  
... ...

-- View the parameters of the DROP TABLE syntax.
gaussdb=#  \h DROP TABLE
Command:     DROP TABLE
Description: remove a table
Syntax:
DROP TABLE [ IF EXISTS ]
    { [schema.]table_name } [, ...] [ CASCADE | RESTRICT ] [ PURGE ];

-- View meta-commands supported by gsql.
gaussdb=#  \?
General
  \copyright             show GaussDB Kernel usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \h(\help) [NAME]              help on syntax of SQL commands, * for all commands
  \q                     quit gsql
... ...

Example 2

Use a meta-command.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
-- Connect to a database. (Replace the database name and port number as required.)
gsql -d postgres -p 8000
gsql ((GaussDB Kernel XXX.X.XXX build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

-- Create a table.
gaussdb=# CREATE SCHEMA HR;
CREATE SCHEMA
gaussdb=# CREATE TABLE HR.areaS(area_ID NUMBER,area_NAME VARCHAR2(25));
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'area_id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

-- View the definition of a table.
gaussdb=#  \d HR.areaS
               Table "hr.areas"
  Column   |         Type          | Modifiers
-----------+-----------------------+-----------
 area_id   | numeric               |
 area_name | character varying(25) |


-- Insert four rows of data into HR.areaS.
gaussdb=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (1, 'Europe');
INSERT 0 1
gaussdb=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (2, 'Americas');
INSERT 0 1
gaussdb=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (3, 'Asia');
INSERT 0 1
gaussdb=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (4, 'Middle East and Africa');
INSERT 0 1

-- View a table.
gaussdb=# SELECT * FROM HR.areaS;
area_id |       area_name
---------+------------------------
       4 | Middle East and Africa
       1 | Europe
       2 | Americas
       3 | Asia
(4 rows)

-- Use the \pset command to display a table in different ways.
gaussdb=# \pset border 2
Border style is 2.

gaussdb=# SELECT * FROM HR.areaS;
+---------+------------------------+
| area_id |       area_name        |
+---------+------------------------+
|       4 | Middle East and Africa |
|       1 | Europe                 |
|       2 | Americas               |
|       3 | Asia                   |
+---------+------------------------+
(4 rows)

gaussdb=# \pset border 0
Border style is 0.

gaussdb=# SELECT * FROM HR.areaS;
area_id       area_name
------- ----------------------
      4 Middle East and Africa
      1 Europe
      2 Americas
      3 Asia
(4 rows)

-- Use a meta-command.
gaussdb=# \a \t \x
Output format is unaligned.
Showing only tuples.
Expanded display is on.

gaussdb=# SELECT * FROM HR.areaS;
area_id|4
area_name|Middle East and Africa

area_id|1
area_name|Europe

area_id|2
area_name|Americas

area_id|3
area_name|Asia

gaussdb=# \a \t \x
Output format is aligned.
Tuples only is off.
Expanded display is off.

gaussdb=# SELECT * FROM HR.areaS;
area_id       area_name
------- ----------------------
      4 Middle East and Africa
      1 Europe
      2 Americas
      3 Asia
(4 rows)

gaussdb=# DROP TABLE HR.areaS;
DROP TABLE

gaussdb=# DROP SCHEMA HR;
DROP SCHEMA