详解PostgreSql数据库对象信息及应用

详解PostgreSql数据库对象信息及应用 目录

• 1. 查询数据库对象
• 1.1 表查询
• 1.2 查询Schema
• 1.3 查询数据库
• 1.7 触发器
• 2. 查询表占用空间
• 2.1 查询表占用空间
• 4. 总结

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数据库应用内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！