Usage Guidelines

Prerequisites

The user using gsql must have the permission to access the database.

Background

You can use the gsql command to connect to the local database or remote database. When connecting to the remote database, enable remote connection on the server. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database > Remotely Connecting to a Database" in the Developer Guide.

Procedure

1. Connect to the GaussDB server using the gsql tool.

   The gsql tool uses the -d parameter to specify the target database name, the -U parameter to specify the database username, the -h parameter to specify the host name, and the -p parameter to specify the port number.

   

   If the database name is not specified, the default database name generated during initialization will be used. If the database username is not specified, the current OS username will be used by default. If a variable does not belong to any parameter (such as -d and -U), and -d is not specified, the variable will be used as the database name. If -d is specified but -U is not specified, the variable will be used as the database username.

   Example 1: Connect to the 8000 port of the local gaussdb database as user omm.

   gsql -d gaussdb -p 8000

   Example 2: Connect to the 8000 port of the remote gaussdb database as user jack.

   gsql -h 10.180.123.163 -d gaussdb -U jack -p 8000

   In a centralized database instance, when connecting to the primary DN, you can use commas (,) to separate the IP addresses of DNs and add them to the end of -h. gsql connects to each IP address in sequence to check whether the current DN is the primary DN. If no, gsql disconnects from the current IP address and attempts to connect to the next IP address until the primary DN is found.

   gsql -h 10.180.123.163,10.180.123.164,10.180.123.165 -d gaussdb -U jack -p 8000

   Example 3: If gaussdb and omm are not parameter values, they are interpreted as the database name and the username, respectively.

   gsql gaussdb omm -p 8000

   Equals

   gsql -d gaussdb -U omm -p 8000

   For details about the gsql parameters, see Command Reference.

2. Run a SQL statement.

   The following takes creating database human_staff as an example:

   1 2	CREATE DATABASE human_staff; CREATE DATABASE

   Ordinarily, input lines end when a command-terminating semicolon is reached. If the command is sent and executed without any error, the command output is displayed on the screen.

3. Execute gsql meta-commands.

   The following takes all GaussDB databases and description information as an example:

   1 2 3 4 5 6 7 8 9 10 11 12

   openGauss=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ----------------+----------+-----------+---------+-------+----------------------- human_resource | omm | SQL_ASCII | C | C | postgres | omm | SQL_ASCII | C | C | template0 | omm | SQL_ASCII | C | C | =c/omm + | | | | | omm=CTc/omm template1 | omm | SQL_ASCII | C | C | =c/omm + | | | | | omm=CTc/omm human_staff | omm | SQL_ASCII | C | C | (5 rows)

   For details about gsql meta-commands, see Meta-Command Reference.

Example

The example shows how to spread a command over several lines of input. Note the prompt change:

1
2
3
4
5

openGauss=# CREATE TABLE HR.areaS(
openGauss(# area_ID   NUMBER,
openGauss(# area_NAME VARCHAR2(25)
openGauss-# )tablespace EXAMPLE;
CREATE TABLE

Query the table definition:

1
2
3
4
5
6

openGauss=# \d HR.areaS
               Table "hr.areas"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 area_id   | numeric               | not null
 area_name | character varying(25) | 

Insert four lines of data into HR.areaS.

1
2
3
4
5
6
7
8

openGauss=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (1, 'Europe');
INSERT 0 1
openGauss=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (2, 'Americas');
INSERT 0 1
openGauss=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (3, 'Asia');
INSERT 0 1
openGauss=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (4, 'Middle East and Africa');
INSERT 0 1

Change the prompt.

1
2

openGauss=# \set PROMPT1 '%n@%m %~%R%#'
omm@[local] openGauss=#

Query the table:

1
2
3
4
5
6
7
8

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

Use the \pset command to display the table in different ways:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

omm@[local] openGauss=# \pset border 2
Border style is 2.
omm@[local] openGauss=# SELECT * FROM HR.areaS;
+---------+------------------------+
| area_id |       area_name        |
+---------+------------------------+
|       1 | Europe                 |
|       2 | Americas               |
|       3 | Asia                   |
|       4 | Middle East and Africa |
+---------+------------------------+
(4 rows)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

omm@[local] openGauss=# \pset border 0
Border style is 0.
omm@[local] openGauss=# SELECT * FROM HR.areaS;
area_id       area_name        
------- ----------------------
      1 Europe
      2 Americas
      3 Asia
      4 Middle East and Africa
(4 rows)

Use the meta-command:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

omm@[local] openGauss=# \a \t \x
Output format is unaligned.
Showing only tuples.
Expanded display is on.
omm@[local] openGauss=# SELECT * FROM HR.areaS;
area_id|2
area_name|Americas

area_id|1
area_name|Europe

area_id|4
area_name|Middle East and Africa

area_id|3
area_name|Asia
omm@[local] openGauss=#