随着国内一些相关政策的推行，国产数据库和开源数据库正以新的身份进军各个领域，我们今天探讨主要是postgres数据库相关的一些内置命令，在postgres数据库中称为“元命令”；接下来就为大家讲讲关于元命令的使用
# postgres数据库元命令讲解
1.列出数据库以及查看数据库编码（\l）
antdb=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+-------------+----------+-------------+-------------+------------------------
antdb | antdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
dtdb | antdb | UTF8 | C | C | =Tc/antdb +
| | | | | antdb=CTc/antdb +
| | | | | role_zwzc_sjgl=c/antdb+
| | | | | role_zwzc_zwgl=c/antdb+
| | | | | role_zwzc_jscj=c/antdb+
| | | | | role_zwzc_zwck=c/antdb+
| | | | | role_yygl_yygl=c/antdb+
| | | | | role_yygl_yywh=c/antdb+
| | | | | role_yygl_gly=c/antdb +
| | | | | role_kfs_kfcs=c/antdb +
| | | | | role_fgs=c/antdb +
| | | | | role_hzhb_sjgl=c/antdb+
| | | | | role_hzhb_yywh=c/antdb+
| | | | | role_hzhb_tscl=c/antdb+
| | | | | role_hzhb_jscj=c/antdb+
| | | | | role_hzhb_gly=c/antdb
keepalived | keepadlived | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | antdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
qddb | antdb | UTF8 | C | C | =Tc/antdb +
| | | | | antdb=CTc/antdb +
| | | | | sp=c/antdb +
| | | | | capes=c/antdb
template0 | antdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/antdb +
| | | | | antdb=CTc/antdb
template1 | antdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/antdb +
| | | | | antdb=CTc/antdb
test1 | yewu | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
yzxdb | antdb | UTF8 | C | C |
zfdb | antdb | UTF8 | C | C |
(10 rows)
关于\l元命令对应的SQL代码为：
********* QUERY **********
/*pg*/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;
**************************2.使用元命令切换数据库（\c）
antdb=# \c dtdb antdb
You are now connected to database "dtdb" as user "antdb".
dtdb=# \c dtdb
You are now connected to database "dtdb" as user "antdb".
注：如果不指定当前用户，默认是你当前登录数据库用户或切换数据库用户3.列出当前数据库所有的表(\dt \dtS+)
dtdb=> \dt
List of relations
Schema | Name | Type | Owner
--------+---------------------+-------+-------
public | dtdb_check_tab | table | antdb
public | t | table | antdb
public | test | table | sp
public | upg_busi_log_202205 | table | antdb
public | upg_busi_log_202206 | table | antdb
(5 rows)
dtdb=> \dtS+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
------------+-------------------------+-------+-------+-------------+------------+-------------
pg_catalog | ora_cast | table | antdb | permanent | 40 kB |
pg_catalog | ora_convert | table | antdb | permanent | 384 kB |
pg_catalog | ora_synonym | table | antdb | permanent | 0 bytes |
pg_catalog | pg_aggregate | table | antdb | permanent | 56 kB |
pg_catalog | pg_am | table | antdb | permanent | 40 kB |
pg_catalog | pg_amop | table | antdb | permanent | 80 kB |
pg_catalog | pg_amproc | table | antdb | permanent | 64 kB |
pg_catalog | pg_attrdef | table | antdb | permanent | 16 kB |
pg_catalog | pg_attribute | table | antdb | permanent | 552 kB |
pg_catalog | pg_auth_members | table | antdb | permanent | 40 kB |
pg_catalog | pg_authid | table | antdb | permanent | 48 kB |
pg_catalog | pg_cast | table | antdb | permanent | 48 kB |
pg_catalog | pg_class | table | antdb | permanent | 144 kB |
pg_catalog | pg_collation | table | antdb | permanent | 296 kB |
pg_catalog | pg_constraint | table | antdb | permanent | 48 kB |
pg_catalog | pg_conversion | table | antdb | permanent | 48 kB |
pg_catalog | pg_database | table | antdb | permanent | 48 kB |
pg_catalog | pg_db_role_setting | table | antdb | permanent | 16 kB |
pg_catalog | pg_default_acl | table | antdb | permanent | 16 kB |
pg_catalog | pg_depend | table | antdb | permanent | 536 kB |
pg_catalog | pg_description | table | antdb | permanent | 344 kB |
pg_catalog | pg_enum | table | antdb | permanent | 0 bytes |
pg_catalog | pg_event_trigger | table | antdb | permanent | 8192 bytes |
pg_catalog | pg_extension | table | antdb | permanent | 48 kB |
pg_catalog | pg_foreign_data_wrapper | table | antdb | permanent | 8192 bytes |
pg_catalog | pg_foreign_server | table | antdb | permanent | 8192 bytes |
pg_catalog | pg_foreign_table | table | antdb | permanent | 8192 bytes |
pg_catalog | pg_index | table | antdb | permanent | 64 kB |
pg_catalog | pg_inherits | table | antdb | permanent | 0 bytes |
pg_catalog | pg_init_privs | table | antdb | permanent | 56 kB |
pg_catalog | pg_language | table | antdb | permanent | 48 kB |
.....
关于\dt元命令对应的SQL：
********* QUERY **********
/*pg*/SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
关于\dtS+元命令对应的SQL：
********* QUERY **********
/*pg*/SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
CASE c.relpersistence WHEN 'p' THEN 'permanent' WHEN 't' THEN 'temporary' WHEN 'u' THEN 'unlogged' END as "Persistence",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
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','p','s','')
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************4.列出当前数据库所有的索引(\di)
dtdb=> \di
List of relations
Schema | Name | Type | Owner | Table
--------+----------------------------------------+-------+-------+---------------------
public | ix_idx_upg_busi_log_1_202205 | index | antdb | upg_busi_log_202205
public | ix_idx_upg_busi_log_2_202205 | index | antdb | upg_busi_log_202205
public | ix_idx_upg_busi_log_3_202205 | index | antdb | upg_busi_log_202205
public | ix_idx_upg_busi_log_4_202205 | index | antdb | upg_busi_log_202205
public | pk_upg_busi_log_202205 | index | antdb | upg_busi_log_202205
public | upg_busi_log_202206_create_date_idx | index | antdb | upg_busi_log_202206
public | upg_busi_log_202206_ext_seq_idx | index | antdb | upg_busi_log_202206
public | upg_busi_log_202206_order_id_idx | index | antdb | upg_busi_log_202206
public | upg_busi_log_202206_pay_seq_idx | index | antdb | upg_busi_log_202206
public | upg_busi_log_202206_payment_log_id_idx | index | antdb | upg_busi_log_202206
(10 rows)
关于元命令\di对应的SQL：
********* QUERY **********
/*pg*/SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c2.relname as "Table"
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 <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************5.列出数据库角色（\du）
dtdb=> \du
List of roles
Role name | Attributes |
Member of
----------------+------------------------------------------------------------+-----------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
aijh | | {role_hzhb_gly,ngad_result}
antdb | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
capes | | {}
keepadlived | | {}
liran1 | | {role_yygl_yygl,sp}
ngad_result | | {}
replicator | Replication | {}
role_fgs | Cannot login | {}
role_hzhb_gly | Cannot login | {}
sp | | {}
yewu | Superuser | {}
关于\du元命令对应的SQL：
********* QUERY **********
/*pg*/SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, 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.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************6.列出当前数据库下的SCHEMA
dtdb=> \dn
List of schemas
Name | Owner
-------------+-------
dbms_lock | antdb
dbms_output | antdb
dbms_random | antdb
public | antdb
sms | sms
(5 rows)
关于\dn元命令对应的SQL：
********* QUERY **********
/*pg*/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;
**************************
7.列出数据库表空间
dtdb=> \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | antdb |
pg_global | antdb |
(2 rows)
关于\db元命令对应相关SQL：
********* QUERY **********
/*pg*/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;
**************************
8.列出数据库所有的 function
dtdb=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
关于\df元命令对应的SQL：
********* QUERY **********
/*pg*/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 p.prokind
WHEN 'a' THEN 'agg'
WHEN 'w' THEN 'window'
WHEN 'p' THEN 'proc'
ELSE 'func'
END as "Type"
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 <> 'information_schema'
ORDER BY 1, 2, 4;
**************************9.列出数据库扩展
dtdb=# \dx
List of installed extensions
Name | Version | Schema | Description
----------+---------+------------+-------------------------------
plorasql | 1.0 | pg_catalog | PL/oraSQL procedural language
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
相关\dx元命令相关的SQL：
********* QUERY **********
/*pg*/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;
**************************10.列出数据库事件触发器
dtdb=# \dy
List of event triggers
Name | Event | Owner | Enabled | Function | Tags
------+-------+-------+---------+----------+------
(0 rows)
关于\dy元命令对应的sql:
********* QUERY **********
/*pg*/SELECT evtname as "Name", evtevent as "Event", pg_catalog.pg_get_userbyid(e.evtowner) as "Owner",
case evtenabled when 'O' then 'enabled' when 'R' then 'replica' when 'A' then 'always' when 'D' then 'disabled' end as "Enabled",
e.evtfoid::pg_catalog.regproc as "Function", pg_catalog.array_to_string(array(select x from pg_catalog.unnest(evttags) as t(x)), ', ') as "Tags"
FROM pg_catalog.pg_event_trigger e ORDER BY 1
**************************11.列出数据库函数定义
dtdb=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
关于\df元命令对应的SQL：
********* QUERY **********
/*pg*/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 p.prokind
WHEN 'a' THEN 'agg'
WHEN 'w' THEN 'window'
WHEN 'p' THEN 'proc'
ELSE 'func'
END as "Type"
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 <> 'information_schema'
ORDER BY 1, 2, 4;
**************************12.列出数据库已安装扩展列表
dtdb=# \dx
List of installed extensions
Name | Version | Schema | Description
----------+---------+------------+-------------------------------
plorasql | 1.0 | pg_catalog | PL/oraSQL procedural language
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
关于\dx元命令对应的SQL：
********* QUERY **********
/*pg*/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;
**************************13.列出数据库分区索引列表
dtdb=# \dPi
List of partitioned indexes
Schema | Name | Owner | Table
--------+------+-------+-------
(0 rows)
关于\dPi元命令对应的SQL：
********* QUERY **********
/*pg*/SELECT n.nspname as "Schema",
c.relname as "Name",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c2.oid::regclass as "Table"
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','')
AND NOT c.relispartition
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY "Schema", "Name";
**************************14.列出数据库分区关系
dtdb=# \dP
List of partitioned relations
Schema | Name | Owner | Type | Table
--------+------+-------+------+-------
(0 rows)
关于\dP元命令对应的SQL:
********* QUERY **********
/*pg*/SELECT n.nspname as "Schema",
c.relname as "Name",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
CASE c.relkind WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
c2.oid::regclass as "Table"
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 ('p','I','')
AND NOT c.relispartition
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY "Schema", "Type" DESC, "Name";
**************************15.列出数据库表索引列表
dtdb=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+----------------------------------------+-------+-------+---------------------
public | ix_idx_upg_busi_log_1_202205 | index | antdb | upg_busi_log_202205
public | ix_idx_upg_busi_log_2_202205 | index | antdb | upg_busi_log_202205
public | ix_idx_upg_busi_log_3_202205 | index | antdb | upg_busi_log_202205
public | ix_idx_upg_busi_log_4_202205 | index | antdb | upg_busi_log_202205
public | pk_upg_busi_log_202205 | index | antdb | upg_busi_log_202205
public | upg_busi_log_202206_create_date_idx | index | antdb | upg_busi_log_202206
public | upg_busi_log_202206_ext_seq_idx | index | antdb | upg_busi_log_202206
public | upg_busi_log_202206_order_id_idx | index | antdb | upg_busi_log_202206
public | upg_busi_log_202206_pay_seq_idx | index | antdb | upg_busi_log_202206
public | upg_busi_log_202206_payment_log_id_idx | index | antdb | upg_busi_log_202206
(10 rows)
关于\di元命令对应的SQL：
********* QUERY **********
/*pg*/SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c2.relname as "Table"
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 <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************16.设置标题（\C title_name）
dtdb=# \C "print upg_busi_log_202206 table xinxi:"
Title is ""print upg_busi_log_202206 table xinxi:"".17.执行当前查询缓冲区（\g）
如果当前查询缓冲区为空，则最新发送的查询将被重新执行。
dtdb=# select * from upg_busi_log_202206 limit 10;
"print upg_busi_log_202206 ta
ble xinxi:"
payment_log_id | order_id | pay_seq | ext_seq | busi_code | dest_id | src_id | account_type | account_code | order_fee | bank_card | cont
ract_no | busi_state | busi_desc | currency | exchange_rate | discount_rate | actual_amount | create_date | done_date | check_date | remarks | state
----------------+--------------+---------+----------------------------------+-----------+---------+--------+--------------+--------------+-----------+-----------+-----
--------+------------+-----------+----------+---------------+---------------+---------------+-------------+------------+------------+---------+-------
100025653921 | 100055891907 | | | 4002 | 95 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100026231083 | 100056693099 | | | 4002 | 91 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025546884 | 100055746380 | | | 4002 | 95 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025546962 | 100055746438 | | | 4002 | 91 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025547049 | 100055746543 | | xxxxx| 2007 | AI | TY | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025547013 | 100055746507 | | xxxxx| 2007 | AI | TY | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025547357 | 100055746845 | | | 4002 | 95 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025553082 | 100055752587 | | | 4002 | 95 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025547537 | 100055747031 | | | 4002 | 05 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025548019 | 100055747494 | | | 4002 | 95 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
(10 rows)
dtdb=# \g
"print upg_busi_log_202206 ta
ble xinxi:"
payment_log_id | order_id | pay_seq | ext_seq | busi_code | dest_id | src_id | account_type | account_code | order_fee | bank_card | cont
ract_no | busi_state | busi_desc | currency | exchange_rate | discount_rate | actual_amount | create_date | done_date | check_date | remarks | state
----------------+--------------+---------+----------------------------------+-----------+---------+--------+--------------+--------------+-----------+-----------+-----
--------+------------+-----------+----------+---------------+---------------+---------------+-------------+------------+------------+---------+-------
100025653921 | 100055891907 | | | 4002 | 95 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100026231083 | 100056693099 | | | 4002 | 91 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025546884 | 100055746380 | | | 4002 | 95 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025546962 | 100055746438 | | | 4002 | 91 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025547049 | 100055746543 | | xxxxx| 2007 | AI | TY | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025547013 | 100055746507 | | xxxxx| 2007 | AI | TY | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025547357 | 100055746845 | | | 4002 | 95 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025553082 | 100055752587 | | | 4002 | 95 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025547537 | 100055747031 | | | 4002 | 05 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
100025548019 | 100055747494 | | | 4002 | 95 | AI | | | | |
| 1 | | | | | | 2024-01-17 | 2024-01-17 | | | U
(10 rows)18.将当前缓冲区打印到标准输出（\p）
dtdb=# \p
select * from upg_busi_log_202206 limit 10;19.重置缓冲区（\r）
dtdb=# \r
Query buffer reset (cleared).20.查看函数代码（\sf function_name）
dtdb=# \sf anyenum_out
********* QUERY **********
/*pg*/SELECT 'anyenum_out'::pg_catalog.regproc::pg_catalog.oid
**************************
********* QUERY **********
/*pg*/SELECT pg_catalog.pg_get_functiondef(3505)
**************************
CREATE OR REPLACE FUNCTION pg_catalog.anyenum_out(anyenum)
RETURNS cstring
LANGUAGE internal
STABLE PARALLEL SAFE STRICT
AS $function$anyenum_out$function$21.显示执行时间（\timing on | off）
dtdb=# \timing on
Timing is on.
dtdb=# \timing off
Timing is off.22.查看字符集编码（\encoding）
dtdb=# \encoding
UTF8
dtdb=# show client_encoding ;
"print upg_busi_log_202206 table xinxi:"
client_encoding
-----------------
UTF8
(1 row)
dtdb=# show server_encoding ;
"print upg_busi_log_202206 table xinxi:"
server_encoding
-----------------
UTF8
(1 row)23.查看最近发生的错误信息（\errverbose）
dtdb=# \errverbose
There is no previous error.24.查看已定义的配置信息（\drds）
dtdb=# \drds
List of settings
Role | Database | Settings
-------+----------+--------------------
aiuap | | log_statement=none
sms | | search_path=sms
| dtdb | log_statement=ddl
(3 rows)
关于\drds元命令对应的SQL：
********* QUERY **********
/*pg*/SELECT rolname AS "Role", datname AS "Database",
pg_catalog.array_to_string(setconfig, E'\n') AS "Settings"
FROM pg_catalog.pg_db_role_setting s
LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole
ORDER BY 1, 2;
**************************25.执行sql文本文件(\i)
执行时需要在sql文件当前路径下执行
\i table_struct.sql26.在数据库执行主机命令(\!)
dtdb=# \! pwd
/app/antdb27.查看当前会话连接信息(\conninfo)
dtdb=# \conninfo
You are connected to database "dtdb" as user "antdb" via socket in "/tmp" at port "5432".28.列模式显示每个字段(\x)
dtdb=# \x
Expanded display is on.
dtdb=# show client_encoding ;
"print upg_busi_log_202206 table xinxi:"
-[ RECORD 1 ]---+-----
client_encoding | UTF8
577
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
