How to Use gsql

Prerequisites

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

Procedure

1. Use gsql to connect to a GaussDB instance.

   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 2: Connect to the 8000 port of the remote postgres database as user jack.

   gsql -h 10.180.123.163 -d postgres -U jack -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 | root | SQL_ASCII | C | C | postgres | root | SQL_ASCII | C | C | template0 | root | SQL_ASCII | C | C | =c/root + | | | | | root=CTc/root template1 | root | SQL_ASCII | C | C | =c/root + | | | | | root=CTc/root human_staff | root | 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(
postgres(# 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

openGauss=# \set PROMPT1 '%n@%m %~%R%#'

Query the table:

1
2
3
4
5
6
7
8

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

openGauss=#\pset border 2
Border style is 2.
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

openGauss=#\pset border 0
Border style is 0.
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

openGauss=#\a \t \x
Output format is unaligned.
Showing only tuples.
Expanded display is on.
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