Meta-Command Reference

\copyright

Displays the version and copyright information of GaussDB.

-

\g [FILE] or ;

Executes the query (and sends the result to a file or pipe).

-

\h(\help) [NAME]

Provides syntax help for a 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 function is enabled and the maximum number of concurrently executed tasks is num.
• off: The function is disabled.

The default value of num is 1024.

\q

Exits the gsql program. In a script file, this command is run only when a script terminates.

-

\e [FILE] [LINE]

Edits the query buffer or file using an external editor.

\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 (clears) the query buffer.

\w FILE

Outputs the current query buffer to a file.

\copy { table [ ( column_list ) ] | ( query ) } { from | to } { filename | stdin | stdout | pstdin | pstdout } [ with ] [ binary ] [ oids ] [ delimiter [ as ] 'character' ] [ null [ as ] 'string' ] [ csv [ header ] [ quote [ as ] 'character' ] [ escape [ as ] 'character' ] [ force quote column_list | * ] [ force not null column_list ] ] [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. This means that the accessibility and permissions of the file are the permissions of the local user rather than the server. The initial database user permission is not required.

\echo [STRING]

Writes a character string to the standard output.

\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

Is similar to \i, but resolves relative path names differently.

\ir+ FILE KEY

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

\o [FILE]

Saves all query results to a file.

\qecho [STRING]

Prints a character string to the query result output.

\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.

-

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

1

openGauss=# \d

\d[S+] NAME

Lists the structure of specified tables, views, and indexes.

-

Lists the structure of table a.

1

openGauss=# \dtable+ a

\d+ [PATTERN]

Lists all tables, views, and indexes.

If PATTERN is specified, only tables, views, and indexes whose names match PATTERN are shown.

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

1

openGauss=# \d+ f*

\da[S] [PATTERN]

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

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

openGauss=# \da f*

\db[+] [PATTERN]

Lists all available tablespaces.

If PATTERN is specified, only tablespaces whose names match PATTERN are shown.

Lists all available tablespaces whose names start with p.

1

openGauss=# \db p*

\dc[S+] [PATTERN]

Lists all available conversions between character sets.

If PATTERN is specified, only conversions whose names match PATTERN are displayed.

Lists all available conversions between character sets.

1

openGauss=# \dc *

\dC[+] [PATTERN]

Lists all available type conversions.

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

If PATTERN is specified, only conversions whose names match PATTERN are displayed.

Lists all type conversions whose pattern names start with c.

1

openGauss=# \dC c*

\dd[S] [PATTERN]

Lists descriptions about objects matching PATTERN.

If no parameter is specified, all visible objects are displayed. The objects include aggregations, functions, operators, types, relations (table, view, index, sequence, and large object), and rules.

Lists all visible objects.

1

openGauss=# \dd

\ddp [PATTERN]

Lists all default permissions.

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

Lists all default permissions.

1

openGauss=# \ddp

\dD[S+] [PATTERN]

Lists all available domains.

If PATTERN is specified, only domains whose names match PATTERN are shown.

Lists all available domains.

1

openGauss=# \dD

\ded[+] [PATTERN]

Lists all data sources.

If PATTERN is specified, only objects whose names match PATTERN are shown.

Lists all data sources.

1

openGauss=# \ded

\det[+] [PATTERN]

Lists all external tables.

If PATTERN is specified, only tables whose names match PATTERN are shown.

Lists all external tables.

1

openGauss=# \det

\des[+] [PATTERN]

Lists all external servers.

If PATTERN is specified, only servers whose names match PATTERN are shown.

Lists all external servers.

1

openGauss=# \des

\deu[+] [PATTERN]

Lists all user mappings.

If PATTERN is specified, only information whose name matches PATTERN is displayed.

Lists all user mappings.

1

openGauss=#\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

openGauss=# \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.

If PATTERN is specified, only functions whose names match PATTERN are displayed.

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

1

openGauss=# \df

\dF[+] [PATTERN]

Lists all text search configurations.

If PATTERN is specified, only configurations whose names match PATTERN are displayed.

Lists all text search configurations.

1

openGauss=# \dF+

\dFd[+] [PATTERN]

Lists all text search dictionaries.

If PATTERN is specified, only dictionaries whose names match PATTERN are displayed.

Lists all text search dictionaries.

1

openGauss=# \dFd

\dFp[+] [PATTERN]

Lists all text search parsers.

If PATTERN is specified, only analyzers whose names match PATTERN are displayed.

Lists all text search parsers.

1

openGauss=# \dFp

\dFt[+] [PATTERN]

Lists all text search templates.

If PATTERN is specified, only templates whose names match PATTERN are displayed.

Lists all text search templates.

1

openGauss=# \dFt

\dg[+] [PATTERN]

Lists all database roles.

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

Lists all database roles whose names start with j and end with e.

1

openGauss=# \dg j?e

\dl

Specifies an alias for \lo_list, which shows a list of large objects.

-

Lists all large objects.

1

openGauss=# \dl

\dL[S+] [PATTERN]

Lists available procedural languages.

If PATTERN is specified, only languages whose names match PATTERN are listed.

Lists available procedural languages.

1

openGauss=# \dL

\dm[S+] [PATTERN]

Lists materialized views.

If PATTERN is specified, only materialized views whose names match PATTERN are displayed.

Lists materialized views.

1

openGauss=# \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

openGauss=# \dn+ d*

\do[S] [PATTERN]

Lists available operators with their operand and return types.

If PATTERN is specified, only operators whose names match PATTERN are displayed. By default, only operators you created are displayed.

Lists available operators with their operand and return types.

1

openGauss=# \do

\dO[S+] [PATTERN]

Lists collations.

If PATTERN is specified, only rules whose names match PATTERN are displayed. By default, only collations you created are displayed.

Lists collations.

1

openGauss=# \dO

\dp [PATTERN]

Lists tables, views, and related permissions.

The following result about \dp is displayed:

rolename=xxxx/yyyy  -- Assigning permissions to a role

=xxxx/yyyy  --Assigning permissions to public

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

If PATTERN is specified, only tables and views whose names match the pattern are shown.

Lists tables, views, and related permissions.

1

openGauss=# \dp

\drds [PATTERN1 [PATTERN2]]

Lists all modified configuration parameters. 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 rules whose names match PATTERN are displayed. If the default value is used or * is specified, all settings are listed.

Lists all modified configuration parameters of the postgres database.

1

openGauss=# \drds *openGausss

\dT[S+] [PATTERN]

Lists all data types.

If PATTERN is specified, only types whose names match PATTERN are shown.

Lists all data types.

1

openGauss=# \dT

\du[+] [PATTERN]

Lists all database roles.

If PATTERN is specified, only roles whose names match PATTERN are listed.

Lists all database roles.

1

openGauss=# \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 a foreign table, index, sequence, table, or view, respectively. You can specify any or a combination of these letters sequenced in any order to obtain an object list. For example, \dit lists all indexes and tables. If a command is suffixed with a plus sign (+), physical dimensions and related descriptions of each object will be displayed.

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

openGauss=# \div

\dx[+] [PATTERN]

Lists installed extensions.

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

Lists installed extensions.

1

openGauss=# \dx

\l[+]

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

-

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

1

openGauss=# \l

\sf[+] FUNCNAME

Shows function definitions.

-

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

1
2
3

openGauss=# \sf function_a
openGauss=# \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 regarded as a regular expression, and only matched tables, views, and sequences are displayed.

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

1

openGauss=# \z

r

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

w

UPDATE: Allows users to update columns in 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 includes in specified schemas.
• For sequences, allows users to use the nextval function.

C

CREATE:

• For databases, allows users to create schemas within a 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 specified objects.

P

DROP: allows users to delete a specified object.

m

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

i

INDEX: allows users to create indexes on specified tables.

v

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

*

Authorization options for preceding permissions

\a

Controls the switchover between unaligned mode and aligned mode.

\C [STRING]

Sets the title of any table being printed as the result of a query or cancels such a setting.

\f [STRING]

Specifies a field separator for unaligned query output.

\H

• If the text format schema is used, switches to the HTML format schema.
• If the HTML format schema is used, switches to the text format.

\pset NAME [VALUE]

Sets options affecting the output of query result tables. For the value of NAME, see Table 7.

\t [on|off]

Switches the information and row count footer of the output column name.

\T [STRING]

Specifies attributes to be placed within the table tag in HTML output format. If the parameter is not configured, the attributes are not configured.

\x [on|off|auto]

Switches expanded table formatting mode.

border

The value must be a number. In general, a larger number indicates wider borders and more table lines.

• The value is an integer greater than 0 in HTML format.
• The value range in other formats is as follows:
  • 0: no border
  • 1: internal dividing line
  • 2: table frame

expanded (or x)

Switches between regular and expanded formats.

• When expanded format is enabled, query results are displayed in two columns, with the column name on the left and the data on the right. This format is useful if the data does not fit the screen in the normal "horizontal" format.
• The expanded format is used when the query output is wider than the screen. Otherwise, the regular format is used. The regular format is effective only in the aligned and wrapped formats.

fieldsep

Specifies the field separator to be used in unaligned output format. In this way, you can create tab- or comma-separated output required by other programs. To set a tab as field separator, type \pset fieldsep '\t'. The default field separator is a vertical bar (|).

-

fieldsep_zero

Specifies the field separator to be used in unaligned output format to zero bytes.

-

footer

Enables or disables the display of table footers.

-

format

Selects the output format. Unique abbreviations are allowed. (This means a single letter is sufficient.)

Value range:

• unaligned: Write all columns of a row on one line, separated by the currently active column separator.
• aligned: This format is standard and human-readable.
• wrapped: This format is similar to aligned, but includes the packaging cross-line width data value to suit the width of the target field output.
• html: This format output table to the markup language for a document. The output is not a complete document.
• latex: This format output table to the markup language for a document. The output is not a complete document.
• troff-ms: This format output table to the markup language for a document. The output is not a complete document.

null

Specifies a string to be printed in place of a null value.

By default, nothing is printed, which can easily be mistaken for an empty character string.

numericlocale

Enables or disables the display of a locale-specific character to separate groups of digits to the left of the decimal marker.

• on: The specified separator is displayed.
• off: The specified separator is not displayed

If this parameter is ignored, the default separator is displayed.

pager

Controls the use of a pager for query and gsql help outputs. If the PAGER environment variable is specified, the output is piped to the specified program. Otherwise, a platform-dependent default 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

Specifies the record separator to be used in unaligned output format.

-

recordsep_zero

Specifies the record separator to be used in unaligned output format to zero bytes.

-

tableattr (or T)

Specifies attributes to be placed inside the HTML table tag in HTML output format (such as cellpadding or bgcolor). Note that you do not need to specify border here because it has been used by \pset border. If no value is given, the table attributes do not need to be set.

-

title

Specifies the table title for any subsequently printed tables. This can be used to give your output descriptive tags. If no value is given, the title does not need to be set.

-

tuples_only (or t)

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

-

feedback

Specifies whether to output the number of result lines.

-

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

Connects to a new database. (The current database is postgres.) 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.

-

\encoding [ENCODING]

Specifies the client character set encoding.

This command shows the current encoding if it has no parameter.

\conninfo

Outputs information about the current database connection.

-

\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 configure the environment variable.

-

\timing [on|off]

Toggles a display of how long each SQL statement takes, in milliseconds.

• on: specifies that the display is enabled.
• off: indicates that the display is disabled.

\! [COMMAND]

Escapes to a separate Unix shell or runs a Unix command.

-

\prompt [TEXT] NAME

Prompts the user to specify the variable name in text format.

\set [NAME [VALUE]]

Sets the NAME internal variable to VALUE. If more than one value is provided, NAME is set to the concatenation of all of them. If only one parameter is provided, the variable is configured with an empty value.

Some common variables are processed differently in gsql and they are combinations of uppercase letters, numbers and underscores. Table 11 is a list of variables that are specially treated.

\unset NAME

Deletes the variable name of gsql.

\set VERBOSITY value

This variable can be set to default, verbose, or terse to control redundant lines of error reports.

Value range: default, verbose, and terse

\set ON_ERROR_STOP value

If this variable is set, the script execution stops immediately. If this script is invoked from another script, that script execution will stop immediately as well. If the primary script is invoked 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.)

Value range: on/off, true/false, yes/no, and 1/0

\set AUTOCOMMIT [on|off]

Sets the auto commit behavior of the current gsql connection. on indicates that auto commit is enabled, and off indicates that auto commit is disabled. 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.

• on: The auto commit is enabled.
• off: The auto commit is disabled.

\set RETRY [retry_times]

Determines whether to enable the retry function if statement execution encounters errors. The parameter retry_times specifies the maximum number of retry times and the default value is 5. Its value ranges from 5 to 10. If the retry function has been enabled, when you run the \set RETRY command again, the retry function will be disabled.

The configuration file retry_errcodes.conf shows a list of errors. If these errors occur, retry is required. This configuration file is placed in the same directory as that for executable gsql programs. This configuration file is configured by the system rather than by users and cannot be modified by the users.

The retry function can be used in the following error scenarios:

• YY002: TCP communication errors. Print information: Connection reset by peer. (reset between DNs)
• YY003: Lock timeout. Print information: Lock wait timeout.../wait transaction xxx sync time exceed xxx.
• YY004: TCP communication error. Print information: Connection timed out.
• YY005: Failed to issue SET commands. Print information: ERROR SET query.
• YY006: Failed to apply for memory. Print information: memory is temporarily unavailable.
• YY007: Communication library error. Print information: Memory allocate error.
• YY008: Communication library error. Print information: No data in buffer.
• YY009: Communication library error. Print information: Close because release memory.
• YY010: Communication library error. Print information: TCP disconnect.
• YY011: Communication library error. Print information: SCTP disconnect. (Due to specification changes, the current version no longer supports the current feature. Do not use this feature.)
• YY012: Communication library error. Print information: Stream closed by remote.
• YY013: Communication library error. Print information: Wait poll unknown error.

If an error occurs, gsql queries connection status of all DNs. If the connection status is abnormal, gsql sleeps for 1 minute and tries again. In this case, the retries in most of the primary/standby switchover scenarios are involved.

Value range of retry_times: 5 to 10

\lo_list

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