openGauss gsql 常用元命令
姚兴安
连接数据库 使用 -E参数可以显示元命令具体执行的SQL信息
[omm@og1 ~]$ gsql -d postgres -p15400 -E
gsql ((openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
\l 显示数据库中数据库信息
openGauss=# \l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+------------+------------+-------------------
mydb | omm | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | omm | UTF8 | en_US.utf8 | en_US.utf8 |
studentdb | omm | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | omm | UTF8 | en_US.utf8 | en_US.utf8 | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | en_US.utf8 | en_US.utf8 | =c/omm +
| | | | | omm=CTc/omm
(5 rows)
\du 同\dg 显示数据库中所有用户和角色
openGauss=# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvalidbegin, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolauditadmin
, r.rolsystemadmin
, r.rolmonitoradmin
, r.roloperatoradmin
, r.rolpolicyadmin
, r.roluseft
, r.rolkind
FROM pg_catalog.pg_roles r
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')
ORDER BY 1;
**************************
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
student | Sysadmin | {}
openGauss=# \dg
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvalidbegin, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolauditadmin
, r.rolsystemadmin
, r.rolmonitoradmin
, r.roloperatoradmin
, r.rolpolicyadmin
, r.roluseft
, r.rolkind
FROM pg_catalog.pg_roles r
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')
ORDER BY 1;
**************************
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
student | Sysadmin | {}
\db 显示数据库中所有表空间信息
openGauss=# \db
********* QUERY **********
SELECT spcname AS "Name",
pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;
**************************
List of tablespaces
Name | Owner | Location
------------+-------+------------------------
pg_default | omm |
pg_global | omm |
student_ts | omm | tablespace/student_ts1
(3 rows)
\dn 显示数据库中所有schema信息
openGauss=# \dn
********* QUERY **********
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
**************************
List of schemas
Name | Owner
----------------+---------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
pkg_service | omm
pmk | omm
public | omm
snapshot | omm
sqladvisor | omm
student | student
(11 rows)
\d 显示当前数据库下相关数据库对象信息(包含表、视图、物化视图、序列、外部表、stream\ contview)
openGauss=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
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",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c.reloptions as "Storage"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','e','o','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'db4ai'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND c.relname not like 'matviewmap_%'
AND c.relname not like 'mlog_%'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner | Storage
--------+--------+-------+-------+----------------------------------
public | test | table | omm | {orientation=row,compression=no}
public | v_test | view | omm |
(2 rows)
\d tablename 查看某个表的详细信息
openGauss=# \d test
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(test)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************
********* QUERY **********
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')
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '16575';
**************************
********* QUERY **********
SELECT * FROM pg_catalog.pg_class WHERE relname = 'gs_encrypted_columns' AND relnamespace = 11;
**************************
********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 176)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
NULL AS indexdef,
NULL AS attfdwoptions,
(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),
(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),
(SELECT h.adgencol
FROM pg_catalog.pg_attrdef h
WHERE h.adrelid = a.attrelid AND h.adnum = a.attnum AND a.atthasdef) AS generated_column
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16575' AND a.attnum > 0 AND NOT a.attisdropped AND a.attkvtype != 4 AND a.attname <> 'tableoid' AND a.attname <> 'tablebucketid'
ORDER BY a.attnum;
**************************
********* QUERY **********
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, i.indisreplident, c2.reltablespace, i.indisusable
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = '16575' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;
**************************
********* QUERY **********
SELECT pol.policyname, pol.policypermissive, trim(pol.policyroles::text, '{}'), pol.policyqual, pol.policycmd
FROM pg_catalog.pg_rlspolicies pol
LEFT JOIN pg_catalog.pg_namespace N on (N.nspname = pol.schemaname)
LEFT JOIN pg_catalog.pg_class C on (pol.tablename = C.relname and C.relnamespace = N.oid)
WHERE C.oid = '16575' ORDER BY 1;
**************************
********* QUERY **********
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;
**************************
********* QUERY **********
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;
**************************
********* QUERY **********
select partkey,partstrategy from pg_partition where parentid = 16575 order by partkey
**************************
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
Indexes:
"idx_id_test" btree (id) TABLESPACE pg_default
openGauss=#
\dt 显示当前数据库中所有的表
openGauss=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
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",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c.reloptions as "Storage"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'db4ai'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND c.relname not like 'matviewmap_%'
AND c.relname not like 'mlog_%'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+-------+----------------------------------
public | test | table | omm | {orientation=row,compression=no}
(1 row)
\dt+ 以扩展方式显示当前数据库所有表信息，比起\dt 多了最后一列描述信息
openGauss=# \dt+
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
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",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
c.reloptions as "Storage",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'db4ai'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND c.relname not like 'matviewmap_%'
AND c.relname not like 'mlog_%'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+------+-------+-------+---------+----------------------------------+-------------
public | test | table | omm | 0 bytes | {orientation=row,compression=no} |
(1 row)
\di 查看当前数据库中索引信息
openGauss=# \di
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
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",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c2.relname as "Table",
c.reloptions as "Storage"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind IN ('i','I','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'db4ai'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND c.relname not like 'matviewmap_%'
AND c.relname not like 'mlog_%'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+-------------+-------+-------+-------+---------
public | idx_id_test | index | omm | test |
(1 row)
\di indexname 查看当前数据库某个索引的信息
openGauss=# \di idx_id_test
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
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",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c2.relname as "Table",
c.reloptions as "Storage"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind IN ('i','I','s','')
AND n.nspname !~ '^pg_toast'
AND c.relname not like 'matviewmap_%'
AND c.relname not like 'mlog_%'
AND c.relname ~ '^(idx_id_test)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+-------------+-------+-------+-------+---------
public | idx_id_test | index | omm | test |
(1 row)
\dv 查看当前数据库视图信息
openGauss=# \dv
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
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",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c.reloptions as "Storage"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'db4ai'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND c.relname not like 'matviewmap_%'
AND c.relname not like 'mlog_%'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner | Storage
--------+--------+------+-------+---------
public | v_test | view | omm |
(1 row)
\ds 查看当前数据库序列信息
openGauss=# \ds
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
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",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c.reloptions as "Storage"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'db4ai'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND c.relname not like 'matviewmap_%'
AND c.relname not like 'mlog_%'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner | Storage
--------+------+----------+-------+---------
public | sq1 | sequence | omm |
(1 row)\df 查看当前数据库函数信息
openGauss=# \df
********* QUERY **********
SELECT n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
CASE
WHEN p.proisagg THEN 'agg'
WHEN p.proiswindow THEN 'window'
WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
ELSE 'normal'
END as "Type" ,
fencedmode as "fencedmode"
,
propackage as "propackage"
,
prokind as "prokind"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'db4ai'
AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
**************************
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
--------+-----------+------------------+--------------------------------------------------+--------+------------+------------+---------
public | fuc_worth | numeric | price numeric, amount integer, OUT worth numeric | normal | f | f | f
(1 row)
\dx 查看已安装的扩展程序信息
openGauss=# \dx
********* QUERY **********
SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
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
ORDER BY 1;
**************************
List of installed extensions
Name | Version | Schema | Description
-----------------+---------+------------+--------------------------------------------------
dist_fdw | 1.0 | pg_catalog | foreign-data wrapper for distfs access
file_fdw | 1.0 | pg_catalog | foreign-data wrapper for flat file access
hdfs_fdw | 1.0 | pg_catalog | foreign-data wrapper for flat file access
hstore | 1.1 | pg_catalog | data type for storing sets of (key, value) pairs
log_fdw | 1.0 | pg_catalog | Foreign Data Wrapper for accessing logging data
mot_fdw | 1.0 | pg_catalog | foreign-data wrapper for MOT access
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
security_plugin | 1.0 | pg_catalog | provides security functionality
(8 rows)
\x 语法 \x[on|off|auto] 设置语句的输出模式，模式为行的方式输出，执行 \x on 切换为以列的方式来显示
openGauss=# \x
Expanded display is on.
openGauss=# \dx
********* QUERY **********
SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
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
ORDER BY 1;
**************************
List of installed extensions
-[ RECORD 1 ]-------------------------------------------------
Name | dist_fdw
Version | 1.0
Schema | pg_catalog
Description | foreign-data wrapper for distfs access
-[ RECORD 2 ]-------------------------------------------------
Name | file_fdw
Version | 1.0
Schema | pg_catalog
Description | foreign-data wrapper for flat file access
-[ RECORD 3 ]-------------------------------------------------
\timing 语法 \timing [on|off] 控制显示SQL的执行时间，默认为off, on 为显示SQL语句的执行时间
openGauss=# select * from test;
id
----
1
(1 row)
openGauss=# \timing on
Timing is on.
openGauss=# select * from test;
id
----
1
(1 row)
Time: 0.352 ms
\h 用于获取SQL语句的帮助，例如 \h merge
openGauss=# \h merge
Command: MERGE
Description: insert, update, or delete rows of a table based upon source data
Syntax:
MERGE [/*+ plan_hint */] INTO table_name [ [ AS ] alias ]
USING { { table_name | view_name } | subquery } [ [ AS ] alias ]
ON ( condition )
[
WHEN MATCHED THEN
UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ WHERE condition ]
]
[
WHEN NOT MATCHED THEN
INSERT { DEFAULT VALUES |
[ ( column_name [, ...] ) ] VALUES ( { expression | DEFAULT } [, ...] ) [, ...] [ WHERE condition ] }
];
? 获取gsql的元命令的帮助
openGauss=# \?
General
\copyright show openGauss 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
\parallel [on [num]|off] toggle status of execute (currently off)
\q quit gsql
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\i+ FILE KEY execute commands from encrypted file
\ir FILE as \i, but relative to location of current script
\ir+ FILE KEY as \i+, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)
! os_command 用于执行操作系统命令，同oracle的 ！，mysql的 system
openGauss-# \! pwd
/home/omm
openGauss-# \! ls
1.sh create_db_tables.sql test.sql
\o filename 用于重定向输出到文件，注意这个不是简单的将屏幕的内容输出到文本，而是将SQL语句正确执行的结果输出到文本
openGauss-# \o test.out
openGauss-# select * from test;
WARNING: Session unused timeout.
FATAL: terminating connection due to administrator command
could not send data to server: Broken pipe
The connection to the server was lost. Attempting reset: Succeeded.
openGauss=# select * from test;
openGauss=# \! cat test.out
id
----
1
(1 row)
openGauss=# select * from pg_tables;
openGauss=# \! cat test.out
id
----
1
(1 row)
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator | created | last_ddl_time
--------------------+-------------------------------+------------+------------+------------+----------+-------------+--------------+-------------------------------+-------------------------------
pg_catalog | pg_statistic | omm | | t | f | f | | |
\i file.sql
\conninfo 显示gsql中显示会话的连接信息
\c[onnect] [DBNAME] 切换数据库
openGauss=# \conninfo
You are connected to database "postgres" as user "omm" via socket in "/opt/huawei/tmp" at port "15400".
openGauss=# \c mydb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "mydb" as user "omm".
mydb=# \conninfo
You are connected to database "mydb" as user "omm" via socket in "/opt/huawei/tmp" at port "15400".
\echo [string] 打印字符串
mydb=# \echo Hello World!
Hello World!
\q 退出gsql
类似资料：