查询表信息
- 使用系统表pg_tables查询数据库所有表的信息。
SELECT * FROM pg_tables;
- 使用gsql的\d+命令查询表结构。
示例：先创建表customer_t1并插入数据。
CREATE TABLE customer_t1
(
c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
)
with (orientation = column,compression=middle)
distribute by hash (c_last_name);
INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES
(6885, 'map', 'Peter'),
(4321, 'river', 'Lily'),
(9527, 'world', 'James');
查询表结构。（若建表时不指定schema，则表的默认schemaname是public）
\d+ customer_t1;
Table "public.customer_t1"
Column | Type | Modifiers | Storage | Stats target | Description
---------------+--------------+-----------+----------+--------------+-------------
c_customer_sk | integer | | plain | |
c_customer_id | character(5) | | extended | |
c_first_name | character(6) | | extended | |
c_last_name | character(8) | | extended | |
Has OIDs: no
Distribute By: HASH(c_last_name)
Location Nodes: ALL DATANODES
Options: orientation=column, compression=middle, colversion=2.0, enable_delta=false
- 使用函数pg_get_tabledef查询表定义。
SELECT * FROM PG_GET_TABLEDEF('customer_t1');
pg_get_tabledef
-----------------------------------------------------------------------------------
SET search_path = tpchobs; +
CREATE TABLE customer_t1 ( +
c_customer_sk integer, +
c_customer_id character(5), +
c_first_name character(6), +
c_last_name character(8) +
) +
WITH (orientation=column, compression=middle, colversion=2.0, enable_delta=false)+
DISTRIBUTE BY HASH(c_last_name) +
TO GROUP group_version1;
(1 row)
- 执行如下命令查询表customer_t1的所有数据。
SELECT * FROM customer_t1;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | map | Peter |
4321 | river | Lily |
9527 | world | James |
(3 rows)
- 使用SELECT查询表customer_t1中某一字段的所有数据。
SELECT c_customer_sk FROM customer_t1;
c_customer_sk
---------------
6885
4321
9527
(3 rows)
- 查询表是否做过表分析，执行如下命令会返回每个表最近一次做analyze的时间，没有返回的则表示没有做过analyze。
select pg_stat_get_last_analyze_time(oid),relname from pg_class where relkind='r';
查询public下的表做表分析的时间：
select pg_stat_get_last_analyze_time(c.oid),c.relname from pg_class c left join pg_namespace n on c.relnamespace = n.oid where c.relkind='r' and n.nspname='public';
pg_stat_get_last_analyze_time | relname
-------------------------------+----------------------
2022-05-17 07:48:26.923782+00 | warehouse_t19
2022-05-17 07:48:26.964512+00 | emp
2022-05-17 07:48:27.016709+00 | test_trigger_src_tbl
2022-05-17 07:48:27.045385+00 | customer
2022-05-17 07:48:27.062486+00 | warehouse_t1
2022-05-17 07:48:27.114884+00 | customer_t1
2022-05-17 07:48:27.172256+00 | product_info_input
2022-05-17 07:48:27.197014+00 | tt1
2022-05-17 07:48:27.212906+00 | timezone_test
(9 rows)
查询表大小
- 查询表的总大小（包含表的索引和数据）。
select pg_size_pretty(pg_total_relation_size('<schemaname>.<tablename>'));
示例：
先在customer_t1创建索引：
CREATE INDEX index1 ON customer_t1 USING btree(c_customer_sk);
然后查询public模式下，customer_t1表的大小。
select pg_size_pretty(pg_total_relation_size('public.customer_t1'));
pg_size_pretty
----------------
264 kB
(1 row)
- 查询表的数据大小（不包括索引）
select pg_size_pretty(pg_relation_size('<schemaname>.<tablename>'));
示例：查询public模式下，customer_t1表的大小。
select pg_size_pretty(pg_relation_size('public.customer_t1'));
pg_size_pretty
----------------
208 kB
(1 row)
- 查询系统中所有表占用空间大小排行
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit xx;
示例1：查询系统中所有表占用空间大小排行前15。
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 15;
table_full_name | size
---------------------------+---------
pg_catalog.pg_attribute | 2048 KB
pg_catalog.pg_rewrite | 1888 KB
pg_catalog.pg_depend | 1464 KB
pg_catalog.pg_proc | 1464 KB
pg_catalog.pg_class | 512 KB
pg_catalog.pg_description | 504 KB
pg_catalog.pg_collation | 360 KB
pg_catalog.pg_statistic | 352 KB
pg_catalog.pg_type | 344 KB
pg_catalog.pg_operator | 224 KB
pg_catalog.pg_amop | 208 KB
public.tt1 | 160 KB
pg_catalog.pg_amproc | 120 KB
pg_catalog.pg_index | 120 KB
pg_catalog.pg_constraint | 112 KB
(15 rows)
示例2：查询public模式下所有表占用空间排行。
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables where table_schema='public'
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20;
table_full_name | size
-----------------------------+---------
public.tt1 | 160 KB
public.product_info_input | 112 KB
public.customer_t1 | 96 KB
public.warehouse_t19 | 48 KB
public.emp | 32 KB
public.customer | 0 bytes
public.test_trigger_src_tbl | 0 bytes
public.warehouse_t1 | 0 bytes
(8 rows)
查询数据库
- 使用gsql的\l元命令查看数据库系统的数据库列表。
\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
gaussdb | Ruby | SQL_ASCII | C | C |
template0 | Ruby | SQL_ASCII | C | C | =c/Ruby +
| | | | | Ruby=CTc/Ruby
template1 | Ruby | SQL_ASCII | C | C | =c/Ruby +
| | | | | Ruby=CTc/Ruby
(3 rows)
说明
l 如果用户在数据库安装的时候没有指定LC_COLLATE、LC_CTYPE参数，则LC_COLLATE、LC_CTYPE参数的默认值为C。
l 如果用户在创建数据库时没有指定LC_COLLATE、LC_CTYPE参数，则默认使用模板数据库的排序顺序及字符分类。
- 通过系统表pg_database查询数据库列表。
SELECT datname FROM pg_database;
datname
-----------
template1
template0
gaussdb
(3 rows)
查询数据库大小
查询数据库的大小。
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
示例：
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
datname | pg_size_pretty
-----------+----------------
template1 | 61 MB
template0 | 61 MB
postgres | 320 MB
(3 rows)