详解PostgreSql数据库对象信息及应用
PostgreSql数据库对象主要有数据库、表、视图、索引、schema、函数、触发器等。PostgreSql提供了information_schema schema，其中包括返回数据库对象的视图。如用户有访问权限，可以也在pg_catalog schema中查询表、视图等对象。
1. 查询数据库对象
下面通过示例分别展示如何查询各种数据库对象。
1.1 表查询
PostgreSql 表信息可以从information_schema.tables 或 pg_catalog.pg_tables 视图中查询：
select * from information_schema.tables;
select * from pg_catalog.pg_tables;
1.2 查询Schema
获取用户当前选择的schema：
select current_schema();
返回数据库中所有schema：
select * from information_schema.schemata;
select * from pg_catalog.pg_namespace
1.3 查询数据库
查询当前选择的数据库：
select current_database();
返回服务器上所有数据库：
select * from pg_catalog.pg_database
1.4 查询视图
查询数据库中所有schema中的所有视图：
select * from information_schema.views
select * from pg_catalog.pg_views;
1.5 查询表的列信息
查询某个表的列信息：
SELECT
*
FROM
information_schema.columns
WHERE
table_name = ’employee’
ORDER BY
ordinal_position;
1.6 查询索引信息
查询数据库中所有索引信息；
select * from pg_catalog.pg_indexes;
1.6 查询函数信息
返回数据库中所有函数。对于用户定义函数，routine_definition 列会有函数体：
select * from information_schema.routines where routine_type = ‘FUNCTION’;
1.7 触发器
查询数据库中所有触发器，action_statemen类别包括触发器body信息：
select * from information_schema.triggers;
2. 查询表占用空间
2.1 查询表占用空间
实际应用中，通常需要表占用磁盘空间情况，我们可以利用系统表实现：
SELECT nspname || ‘.’ || relname AS “relation”,
pg_size_pretty(pg_total_relation_size(C.oid)) AS “total_size”
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN (‘pg_catalog’, ‘information_schema’)
AND C.relkind <> ‘i’
AND nspname !~ ‘^pg_toast’
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 5;
示例输出：
| relation | total_size |
|---|---|
| public.snapshots | 823 MB |
| public.invoice_items | 344 MB |
| public.messages | 267 MB |
| public.topics | 40 MB |
| public.invoices | 35 MB |
(5 rows)
2.2 查询数据库占用空间
SELECT
pg_database.datname AS “database_name”,
pg_size_pretty(pg_database_size (pg_database.datname)) AS size_in_mb
FROM
pg_database
ORDER BY
size_in_mb DESC;
2.3 查询表的记录数
可以通过统计系统表进行查询：
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 12;
顺便说下MySQL对于查询，读者可以对比学习：
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = (SELECT database())
ORDER BY table_rows DESC
LIMIT 12;
4. 系统表和系统视图
查看数据库系统表命令：
\dt pg_*
| 表名字 | 用途 |
|---|---|
| pg_aggregate | 聚集函数 |
| pg_am | 索引访问方法 |
| pg_amop | 访问方法操作符 |
| pg_amproc | 访问方法支持过程 |
| pg_attrdef | 字段缺省值 |
| pg_attribute | 表的列(也称为”属性”或”字段”) |
| pg_authid | 认证标识符(角色) |
| pg_auth_members | 认证标识符成员关系 |
| pg_autovacuum | 每个关系一个的自动清理配置参数 |
| pg_cast | 转换(数据类型转换) |
| pg_class | 表、索引、序列、视图(“关系”) |
| pg_constraint | 检查约束、唯一约束、主键约束、外键约束 |
| pg_conversion | 编码转换信息 |
| pg_database | 本集群内的数据库 |
| pg_depend | 数据库对象之间的依赖性 |
| pg_description | 数据库对象的描述或注释 |
| pg_index | 附加的索引信息 |
| pg_inherits | 表继承层次 |
| pg_language | 用于写函数的语言 |
| pg_largeobject | 大对象 |
| pg_listener | 异步通知 |
| pg_namespace | 模式 |
| pg_opclass | 索引访问方法操作符类 |
| pg_operator | 操作符 |
| pg_pltemplate | 过程语言使用的模板数据 |
| pg_proc | 函数和过程 |
| pg_rewrite | 查询重写规则 |
| pg_shdepend | 在共享对象上的依赖性 |
| pg_shdescription | 共享对象上的注释 |
| pg_statistic | 优化器统计 |
| pg_tablespace | 这个数据库集群里面的表空间 |
| pg_trigger | 触发器 |
| pg_type | 数据类型 |
列出所有pg开头的系统示图：
\dv pg_*
| 视图名 | 用途 |
|---|---|
| pg_cursors | 打开的游标 |
| pg_group | 数据库用户的组 |
| pg_indexes | 索引 |
| pg_locks | 当前持有的锁 |
| pg_prepared_statements | 预备语句 |
| pg_prepared_xacts | 预备事务 |
| pg_roles | 数据库角色 |
| pg_rules | 规则 |
| pg_settings | 参数设置 |
| pg_shadow | 数据库用户 |
| pg_stats | 规划器统计 |
| pg_tables | 表 |
| pg_timezone_abbrevs | 时区缩写 |
| pg_timezone_names | 时区名 |
| pg_user | 数据库用户 |
| pg_views | 视图 |
4. 总结
本文介绍PostgreSQL系统表及视图；通过系统表或视图查询数据库对象及常用统计信息。
到此这篇关于PostgreSql数据库对象信息及应用的文章就介绍到这了,更多相关PostgreSql数据库应用内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
