openGauss gsql 常用元命令

连接数据库 使用 -E参数可以显示元命令具体执行的SQL信息

1.  
   [omm@og1 ~]$ gsql -d postgres -p15400 -E
2.  
   gsql ((openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr )
3.  
   Non-SSL connection (SSL connection is recommended when requiring high-security)
4.  
   Type "help" for help.

\l 显示数据库中数据库信息

1.  
   openGauss=# \l
2.  
   ********* QUERY **********
3.  
   SELECT d.datname as "Name",
4.  
   pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
5.  
   pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
6.  
   d.datcollate as "Collate",
7.  
   d.datctype as "Ctype",
8.  
   pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
9.  
   FROM pg_catalog.pg_database d
10.  
    ORDER BY 1;
11.  
    **************************
12.  
     
13.  
    List of databases
14.  
    Name | Owner | Encoding | Collate | Ctype | Access privileges
15.  
    ----------- ------- ---------- ------------ ------------ -------------------
16.  
    mydb | omm | UTF8 | en_US.utf8 | en_US.utf8 |
17.  
    postgres | omm | UTF8 | en_US.utf8 | en_US.utf8 |
18.  
    studentdb | omm | UTF8 | en_US.utf8 | en_US.utf8 |
19.  
    template0 | omm | UTF8 | en_US.utf8 | en_US.utf8 | =c/omm
20.  
    | | | | | omm=CTc/omm
21.  
    template1 | omm | UTF8 | en_US.utf8 | en_US.utf8 | =c/omm
22.  
    | | | | | omm=CTc/omm
23.  
    (5 rows)

\du 同\dg 显示数据库中所有用户和角色

1.  
   openGauss=# \du
2.  
   ********* QUERY **********
3.  
   SELECT r.rolname, r.rolsuper, r.rolinherit,
4.  
   r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
5.  
   r.rolconnlimit, r.rolvalidbegin, r.rolvaliduntil,
6.  
   ARRAY(SELECT b.rolname
7.  
   FROM pg_catalog.pg_auth_members m
8.  
   JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
9.  
   WHERE m.member = r.oid) as memberof
10.  
    , r.rolreplication
11.  
    , r.rolauditadmin
12.  
    , r.rolsystemadmin
13.  
    , r.rolmonitoradmin
14.  
    , r.roloperatoradmin
15.  
    , r.rolpolicyadmin
16.  
    , r.roluseft
17.  
    , r.rolkind
18.  
    FROM pg_catalog.pg_roles r
19.  
    WHERE r.rolname not in ('gs_role_copy_files', 'gs_role_signal_backend', 'gs_role_tablespace', 'gs_role_replication', 'gs_role_account_lock', 'gs_role_pldebugger')
20.  
    ORDER BY 1;
21.  
    **************************
22.  
     
23.  
    List of roles
24.  
    Role name | Attributes | Member of
25.  
    ----------- ------------------------------------------------------------------------------------------------------------------ -----------
26.  
    omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
27.  
    student | Sysadmin | {}
28.  
     
29.  
    openGauss=# \dg
30.  
    ********* QUERY **********
31.  
    SELECT r.rolname, r.rolsuper, r.rolinherit,
32.  
    r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
33.  
    r.rolconnlimit, r.rolvalidbegin, r.rolvaliduntil,
34.  
    ARRAY(SELECT b.rolname
35.  
    FROM pg_catalog.pg_auth_members m
36.  
    JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
37.  
    WHERE m.member = r.oid) as memberof
38.  
    , r.rolreplication
39.  
    , r.rolauditadmin
40.  
    , r.rolsystemadmin
41.  
    , r.rolmonitoradmin
42.  
    , r.roloperatoradmin
43.  
    , r.rolpolicyadmin
44.  
    , r.roluseft
45.  
    , r.rolkind
46.  
    FROM pg_catalog.pg_roles r
47.  
    WHERE r.rolname not in ('gs_role_copy_files', 'gs_role_signal_backend', 'gs_role_tablespace', 'gs_role_replication', 'gs_role_account_lock', 'gs_role_pldebugger')
48.  
    ORDER BY 1;
49.  
    **************************
50.  
     
51.  
    List of roles
52.  
    Role name | Attributes | Member of
53.  
    ----------- ------------------------------------------------------------------------------------------------------------------ -----------
54.  
    omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
55.  
    student | Sysadmin | {}

\db 显示数据库中所有表空间信息

1.  
   openGauss=# \db
2.  
   ********* QUERY **********
3.  
   SELECT spcname AS "Name",
4.  
   pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
5.  
   pg_catalog.pg_tablespace_location(oid) AS "Location"
6.  
   FROM pg_catalog.pg_tablespace
7.  
   ORDER BY 1;
8.  
   **************************
9.  
    
10.  
    List of tablespaces
11.  
    Name | Owner | Location
12.  
    ------------ ------- ------------------------
13.  
    pg_default | omm |
14.  
    pg_global | omm |
15.  
    student_ts | omm | tablespace/student_ts1
16.  
    (3 rows)

\dn 显示数据库中所有schema信息

1.  
   openGauss=# \dn
2.  
   ********* QUERY **********
3.  
   SELECT n.nspname AS "Name",
4.  
   pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
5.  
   FROM pg_catalog.pg_namespace n
6.  
   WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
7.  
   ORDER BY 1;
8.  
   **************************
9.  
    
10.  
    List of schemas
11.  
    Name | Owner
12.  
    ---------------- ---------
13.  
    blockchain | omm
14.  
    cstore | omm
15.  
    db4ai | omm
16.  
    dbe_perf | omm
17.  
    dbe_pldebugger | omm
18.  
    pkg_service | omm
19.  
    pmk | omm
20.  
    public | omm
21.  
    snapshot | omm
22.  
    sqladvisor | omm
23.  
    student | student
24.  
    (11 rows)

\d 显示当前数据库下相关数据库对象信息(包含表、视图、物化视图、序列、外部表、stream\ contview)

1.  
   openGauss=# \d
2.  
   ********* QUERY **********
3.  
   SELECT n.nspname as "Schema",
4.  
   c.relname as "Name",
5.  
   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view' WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
6.  
   pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
7.  
   c.reloptions as "Storage"
8.  
   FROM pg_catalog.pg_class c
9.  
   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
10.  
    WHERE c.relkind IN ('r','v','m','S','f','e','o','')
11.  
    AND n.nspname <> 'pg_catalog'
12.  
    AND n.nspname <> 'db4ai'
13.  
    AND n.nspname <> 'information_schema'
14.  
    AND n.nspname !~ '^pg_toast'
15.  
    AND c.relname not like 'matviewmap_%'
16.  
    AND c.relname not like 'mlog_%'
17.  
    AND pg_catalog.pg_table_is_visible(c.oid)
18.  
    ORDER BY 1,2;
19.  
    **************************
20.  
     
21.  
    List of relations
22.  
    Schema | Name | Type | Owner | Storage
23.  
    -------- -------- ------- ------- ----------------------------------
24.  
    public | test | table | omm | {orientation=row,compression=no}
25.  
    public | v_test | view | omm |
26.  
    (2 rows)

\d tablename 查看某个表的详细信息

1.  
   openGauss=# \d test
2.  
   ********* QUERY **********
3.  
   SELECT c.oid,
4.  
   n.nspname,
5.  
   c.relname
6.  
   FROM pg_catalog.pg_class c
7.  
   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
8.  
   WHERE c.relname ~ '^(test)$'
9.  
   AND pg_catalog.pg_table_is_visible(c.oid)
10.  
    ORDER BY 2, 3;
11.  
    **************************
12.  
     
13.  
    ********* QUERY **********
14.  
    SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence,c.relhasclusterkey, c.relreplident, (select count(1) as haspolicy from pg_catalog.pg_class WHERE relname = 'pg_rlspolicy')
15.  
    FROM pg_catalog.pg_class c
16.  
    LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
17.  
    WHERE c.oid = '16575';
18.  
    **************************
19.  
     
20.  
    ********* QUERY **********
21.  
    SELECT * FROM pg_catalog.pg_class WHERE relname = 'gs_encrypted_columns' AND relnamespace = 11;
22.  
    **************************
23.  
     
24.  
    ********* QUERY **********
25.  
    SELECT a.attname,
26.  
    pg_catalog.format_type(a.atttypid, a.atttypmod),
27.  
    (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 176)
28.  
    FROM pg_catalog.pg_attrdef d
29.  
    WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
30.  
    a.attnotnull, a.attnum,
31.  
    (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
32.  
    WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
33.  
    NULL AS indexdef,
34.  
    NULL AS attfdwoptions,
35.  
    (SELECT pg_catalog.format_type (a.atttypmod, g.data_type_original_mod) AS clientlogic_original_type FROM gs_encrypted_columns g WHERE g.column_name = a.attname AND g.rel_id = 16575group by g.data_type_original_oid, g.data_type_original_mod),
36.  
    (SELECT g.data_type_original_oid AS clientlogic_original_type_oid FROM gs_encrypted_columns g WHERE g.column_name = a.attname AND g.rel_id = 16575group by g.data_type_original_oid, g.data_type_original_mod),
37.  
    (SELECT h.adgencol
38.  
    FROM pg_catalog.pg_attrdef h
39.  
    WHERE h.adrelid = a.attrelid AND h.adnum = a.attnum AND a.atthasdef) AS generated_column
40.  
    FROM pg_catalog.pg_attribute a
41.  
    WHERE a.attrelid = '16575' AND a.attnum > 0 AND NOT a.attisdropped AND a.attkvtype != 4 AND a.attname <> 'tableoid' AND a.attname <> 'tablebucketid'
42.  
    ORDER BY a.attnum;
43.  
    **************************
44.  
     
45.  
    ********* QUERY **********
46.  
    SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
47.  
    pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, i.indisreplident, c2.reltablespace, i.indisusable
48.  
    FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
49.  
    LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
50.  
    WHERE c.oid = '16575' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
51.  
    ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;
52.  
    **************************
53.  
     
54.  
    ********* QUERY **********
55.  
    SELECT pol.policyname, pol.policypermissive, trim(pol.policyroles::text, '{}'), pol.policyqual, pol.policycmd
56.  
    FROM pg_catalog.pg_rlspolicies pol
57.  
    LEFT JOIN pg_catalog.pg_namespace N on (N.nspname = pol.schemaname)
58.  
    LEFT JOIN pg_catalog.pg_class C on (pol.tablename = C.relname and C.relnamespace = N.oid)
59.  
    WHERE C.oid = '16575' ORDER BY 1;
60.  
    **************************
61.  
     
62.  
    ********* QUERY **********
63.  
    SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '16575' ORDER BY inhseqno;
64.  
    **************************
65.  
     
66.  
    ********* QUERY **********
67.  
    SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '16575' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
68.  
    **************************
69.  
     
70.  
    ********* QUERY **********
71.  
    select partkey,partstrategy from pg_partition where parentid = 16575 order by partkey
72.  
    **************************
73.  
     
74.  
    Table "public.test"
75.  
    Column | Type | Modifiers
76.  
    -------- --------- -----------
77.  
    id | integer |
78.  
    Indexes:
79.  
    "idx_id_test" btree (id) TABLESPACE pg_default
80.  
     
81.  
    openGauss=#

\dt 显示当前数据库中所有的表

1.  
   openGauss=# \dt
2.  
   ********* QUERY **********
3.  
   SELECT n.nspname as "Schema",
4.  
   c.relname as "Name",
5.  
   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view' WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
6.  
   pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
7.  
   c.reloptions as "Storage"
8.  
   FROM pg_catalog.pg_class c
9.  
   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
10.  
    WHERE c.relkind IN ('r','')
11.  
    AND n.nspname <> 'pg_catalog'
12.  
    AND n.nspname <> 'db4ai'
13.  
    AND n.nspname <> 'information_schema'
14.  
    AND n.nspname !~ '^pg_toast'
15.  
    AND c.relname not like 'matviewmap_%'
16.  
    AND c.relname not like 'mlog_%'
17.  
    AND pg_catalog.pg_table_is_visible(c.oid)
18.  
    ORDER BY 1,2;
19.  
    **************************
20.  
     
21.  
    List of relations
22.  
    Schema | Name | Type | Owner | Storage
23.  
    -------- ------ ------- ------- ----------------------------------
24.  
    public | test | table | omm | {orientation=row,compression=no}
25.  
    (1 row)

\dt 以扩展方式显示当前数据库所有表信息，比起\dt 多了最后一列描述信息

1.  
   openGauss=# \dt
2.  
   ********* QUERY **********
3.  
   SELECT n.nspname as "Schema",
4.  
   c.relname as "Name",
5.  
   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view' WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
6.  
   pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
7.  
   pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
8.  
   c.reloptions as "Storage",
9.  
   pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
10.  
    FROM pg_catalog.pg_class c
11.  
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
12.  
    WHERE c.relkind IN ('r','')
13.  
    AND n.nspname <> 'pg_catalog'
14.  
    AND n.nspname <> 'db4ai'
15.  
    AND n.nspname <> 'information_schema'
16.  
    AND n.nspname !~ '^pg_toast'
17.  
    AND c.relname not like 'matviewmap_%'
18.  
    AND c.relname not like 'mlog_%'
19.  
    AND pg_catalog.pg_table_is_visible(c.oid)
20.  
    ORDER BY 1,2;
21.  
    **************************
22.  
     
23.  
    List of relations
24.  
    Schema | Name | Type | Owner | Size | Storage | Description
25.  
    -------- ------ ------- ------- --------- ---------------------------------- -------------
26.  
    public | test | table | omm | 0 bytes | {orientation=row,compression=no} |
27.  
    (1 row)

\di 查看当前数据库中索引信息

1.  
   openGauss=# \di
2.  
   ********* QUERY **********
3.  
   SELECT n.nspname as "Schema",
4.  
   c.relname as "Name",
5.  
   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view' WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
6.  
   pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
7.  
   c2.relname as "Table",
8.  
   c.reloptions as "Storage"
9.  
   FROM pg_catalog.pg_class c
10.  
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
11.  
    LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
12.  
    LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
13.  
    WHERE c.relkind IN ('i','I','')
14.  
    AND n.nspname <> 'pg_catalog'
15.  
    AND n.nspname <> 'db4ai'
16.  
    AND n.nspname <> 'information_schema'
17.  
    AND n.nspname !~ '^pg_toast'
18.  
    AND c.relname not like 'matviewmap_%'
19.  
    AND c.relname not like 'mlog_%'
20.  
    AND pg_catalog.pg_table_is_visible(c.oid)
21.  
    ORDER BY 1,2;
22.  
    **************************
23.  
     
24.  
    List of relations
25.  
    Schema | Name | Type | Owner | Table | Storage
26.  
    -------- ------------- ------- ------- ------- ---------
27.  
    public | idx_id_test | index | omm | test |
28.  
    (1 row)

\di indexname 查看当前数据库某个索引的信息

1.  
   openGauss=# \di idx_id_test
2.  
   ********* QUERY **********
3.  
   SELECT n.nspname as "Schema",
4.  
   c.relname as "Name",
5.  
   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view' WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
6.  
   pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
7.  
   c2.relname as "Table",
8.  
   c.reloptions as "Storage"
9.  
   FROM pg_catalog.pg_class c
10.  
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
11.  
    LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
12.  
    LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
13.  
    WHERE c.relkind IN ('i','I','s','')
14.  
    AND n.nspname !~ '^pg_toast'
15.  
    AND c.relname not like 'matviewmap_%'
16.  
    AND c.relname not like 'mlog_%'
17.  
    AND c.relname ~ '^(idx_id_test)$'
18.  
    AND pg_catalog.pg_table_is_visible(c.oid)
19.  
    ORDER BY 1,2;
20.  
    **************************
21.  
     
22.  
    List of relations
23.  
    Schema | Name | Type | Owner | Table | Storage
24.  
    -------- ------------- ------- ------- ------- ---------
25.  
    public | idx_id_test | index | omm | test |
26.  
    (1 row)

\dv 查看当前数据库视图信息

1.  
   openGauss=# \dv
2.  
   ********* QUERY **********
3.  
   SELECT n.nspname as "Schema",
4.  
   c.relname as "Name",
5.  
   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view' WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
6.  
   pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
7.  
   c.reloptions as "Storage"
8.  
   FROM pg_catalog.pg_class c
9.  
   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
10.  
    WHERE c.relkind IN ('v','')
11.  
    AND n.nspname <> 'pg_catalog'
12.  
    AND n.nspname <> 'db4ai'
13.  
    AND n.nspname <> 'information_schema'
14.  
    AND n.nspname !~ '^pg_toast'
15.  
    AND c.relname not like 'matviewmap_%'
16.  
    AND c.relname not like 'mlog_%'
17.  
    AND pg_catalog.pg_table_is_visible(c.oid)
18.  
    ORDER BY 1,2;
19.  
    **************************
20.  
     
21.  
    List of relations
22.  
    Schema | Name | Type | Owner | Storage
23.  
    -------- -------- ------ ------- ---------
24.  
    public | v_test | view | omm |
25.  
    (1 row)

\ds 查看当前数据库序列信息

1.  
   openGauss=# \ds
2.  
   ********* QUERY **********
3.  
   SELECT n.nspname as "Schema",
4.  
   c.relname as "Name",
5.  
   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view' WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
6.  
   pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
7.  
   c.reloptions as "Storage"
8.  
   FROM pg_catalog.pg_class c
9.  
   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
10.  
    WHERE c.relkind IN ('S','')
11.  
    AND n.nspname <> 'pg_catalog'
12.  
    AND n.nspname <> 'db4ai'
13.  
    AND n.nspname <> 'information_schema'
14.  
    AND n.nspname !~ '^pg_toast'
15.  
    AND c.relname not like 'matviewmap_%'
16.  
    AND c.relname not like 'mlog_%'
17.  
    AND pg_catalog.pg_table_is_visible(c.oid)
18.  
    ORDER BY 1,2;
19.  
    **************************
20.  
     
21.  
    List of relations
22.  
    Schema | Name | Type | Owner | Storage
23.  
    -------- ------ ---------- ------- ---------
24.  
    public | sq1 | sequence | omm |
25.  
    (1 row)

\df 查看当前数据库函数信息

1.  
   openGauss=# \df
2.  
   ********* QUERY **********
3.  
   SELECT n.nspname as "Schema",
4.  
   p.proname as "Name",
5.  
   pg_catalog.pg_get_function_result(p.oid) as "Result data type",
6.  
   pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
7.  
   CASE
8.  
   WHEN p.proisagg THEN 'agg'
9.  
   WHEN p.proiswindow THEN 'window'
10.  
    WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
11.  
    ELSE 'normal'
12.  
    END as "Type" ,
13.  
    fencedmode as "fencedmode"
14.  
    ,
15.  
    propackage as "propackage"
16.  
    ,
17.  
    prokind as "prokind"
18.  
     
19.  
    FROM pg_catalog.pg_proc p
20.  
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
21.  
    WHERE pg_catalog.pg_function_is_visible(p.oid)
22.  
    AND n.nspname <> 'pg_catalog'
23.  
    AND n.nspname <> 'db4ai'
24.  
    AND n.nspname <> 'information_schema'
25.  
    ORDER BY 1, 2, 4;
26.  
    **************************
27.  
     
28.  
    List of functions
29.  
    Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
30.  
    -------- ----------- ------------------ -------------------------------------------------- -------- ------------ ------------ ---------
31.  
    public | fuc_worth | numeric | price numeric, amount integer, OUT worth numeric | normal | f | f | f
32.  
    (1 row)

\dx 查看已安装的扩展程序信息

1.  
   openGauss=# \dx
2.  
   ********* QUERY **********
3.  
   SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
4.  
   FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
5.  
   ORDER BY 1;
6.  
   **************************
7.  
    
8.  
   List of installed extensions
9.  
   Name | Version | Schema | Description
10.  
    ----------------- --------- ------------ --------------------------------------------------
11.  
    dist_fdw | 1.0 | pg_catalog | foreign-data wrapper for distfs access
12.  
    file_fdw | 1.0 | pg_catalog | foreign-data wrapper for flat file access
13.  
    hdfs_fdw | 1.0 | pg_catalog | foreign-data wrapper for flat file access
14.  
    hstore | 1.1 | pg_catalog | data type for storing sets of (key, value) pairs
15.  
    log_fdw | 1.0 | pg_catalog | Foreign Data Wrapper for accessing logging data
16.  
    mot_fdw | 1.0 | pg_catalog | foreign-data wrapper for MOT access
17.  
    plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
18.  
    security_plugin | 1.0 | pg_catalog | provides security functionality
19.  
    (8 rows)

\x 语法 \x[on|off|auto] 设置语句的输出模式，模式为行的方式输出，执行 \x on 切换为以列的方式来显示

1.  
   openGauss=# \x
2.  
   Expanded display is on.
3.  
   openGauss=# \dx
4.  
   ********* QUERY **********
5.  
   SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
6.  
   FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
7.  
   ORDER BY 1;
8.  
   **************************
9.  
    
10.  
    List of installed extensions
11.  
    -[ RECORD 1 ]-------------------------------------------------
12.  
    Name | dist_fdw
13.  
    Version | 1.0
14.  
    Schema | pg_catalog
15.  
    Description | foreign-data wrapper for distfs access
16.  
    -[ RECORD 2 ]-------------------------------------------------
17.  
    Name | file_fdw
18.  
    Version | 1.0
19.  
    Schema | pg_catalog
20.  
    Description | foreign-data wrapper for flat file access
21.  
    -[ RECORD 3 ]-------------------------------------------------

\timing 语法 \timing [on|off] 控制显示SQL的执行时间，默认为off, on 为显示SQL语句的执行时间

1.  
   openGauss=# select * from test;
2.  
   id
3.  
   ----
4.  
   1
5.  
   (1 row)
6.  
    
7.  
   openGauss=# \timing on
8.  
   Timing is on.
9.  
   openGauss=# select * from test;
10.  
    id
11.  
    ----
12.  
    1
13.  
    (1 row)
14.  
     
15.  
    Time: 0.352 ms

\h 用于获取SQL语句的帮助，例如 \h merge

1.  
   openGauss=# \h merge
2.  
   Command: MERGE
3.  
   Description: insert, update, or delete rows of a table based upon source data
4.  
   Syntax:
5.  
   MERGE [/* plan_hint */] INTO table_name [ [ AS ] alias ]
6.  
   USING { { table_name | view_name } | subquery } [ [ AS ] alias ]
7.  
   ON ( condition )
8.  
   [
9.  
   WHEN MATCHED THEN
10.  
    UPDATE SET { column_name = { expression | DEFAULT } |
11.  
    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
12.  
    [ WHERE condition ]
13.  
    ]
14.  
    [
15.  
    WHEN NOT MATCHED THEN
16.  
    INSERT { DEFAULT VALUES |
17.  
    [ ( column_name [, ...] ) ] VALUES ( { expression | DEFAULT } [, ...] ) [, ...] [ WHERE condition ] }
18.  
    ];

? 获取gsql的元命令的帮助

1.  
   openGauss=# \?
2.  
   General
3.  
   \copyright show openGauss usage and distribution terms
4.  
   \g [FILE] or ; execute query (and send results to file or |pipe)
5.  
   \h(\help) [NAME] help on syntax of SQL commands, * for all commands
6.  
   \parallel [on [num]|off] toggle status of execute (currently off)
7.  
   \q quit gsql
8.  
    
9.  
   Query Buffer
10.  
    \e [FILE] [LINE] edit the query buffer (or file) with external editor
11.  
    \ef [FUNCNAME [LINE]] edit function definition with external editor
12.  
    \p show the contents of the query buffer
13.  
    \r reset (clear) the query buffer
14.  
    \w FILE write query buffer to file
15.  
     
16.  
    Input/Output
17.  
    \copy ... perform SQL COPY with data stream to the client host
18.  
    \echo [STRING] write string to standard output
19.  
    \i FILE execute commands from file
20.  
    \i FILE KEY execute commands from encrypted file
21.  
    \ir FILE as \i, but relative to location of current script
22.  
    \ir FILE KEY as \i , but relative to location of current script
23.  
    \o [FILE] send all query results to file or |pipe
24.  
    \qecho [STRING] write string to query output stream (see \o)

! os_command 用于执行操作系统命令，同oracle的 ！，mysql的 system

1.  
   openGauss-# \! pwd
2.  
   /home/omm
3.  
   openGauss-# \! ls
4.  
   1.sh create_db_tables.sql test.sql

\o filename 用于重定向输出到文件，注意这个不是简单的将屏幕的内容输出到文本，而是将SQL语句正确执行的结果输出到文本

1.  
   openGauss-# \o test.out
2.  
   openGauss-# select * from test;
3.  
   WARNING: Session unused timeout.
4.  
   FATAL: terminating connection due to administrator command
5.  
   could not send data to server: Broken pipe
6.  
   The connection to the server was lost. Attempting reset: Succeeded.
7.  
   openGauss=# select * from test;
8.  
   openGauss=# \! cat test.out
9.  
   id
10.  
    ----
11.  
    1
12.  
    (1 row)
13.  
     
14.  
    openGauss=# select * from pg_tables;
15.  
    openGauss=# \! cat test.out
16.  
    id
17.  
    ----
18.  
    1
19.  
    (1 row)
20.  
     
21.  
    schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator | created | last_ddl_time
22.  
    -------------------- ------------------------------- ------------ ------------ ------------ ---------- ------------- -------------- ------------------------------- -------------------------------
23.  
    pg_catalog | pg_statistic | omm | | t | f | f | | |
24.  
     
25.  
    \i file.sql

\conninfo 显示gsql中显示会话的连接信息

\c[onnect] [DBNAME] 切换数据库

1.  
   openGauss=# \conninfo
2.  
   You are connected to database "postgres" as user "omm" via socket in "/opt/huawei/tmp" at port "15400".
3.  
   openGauss=# \c mydb
4.  
   Non-SSL connection (SSL connection is recommended when requiring high-security)
5.  
   You are now connected to database "mydb" as user "omm".
6.  
   mydb=# \conninfo
7.  
   You are connected to database "mydb" as user "omm" via socket in "/opt/huawei/tmp" at port "15400".

\echo [string] 打印字符串

1.  
   mydb=# \echo Hello World!
2.  
   Hello World!

\q 退出gsql

这篇好文章是转载于：学新通技术网