Skip to main content
 首页 » 数据库

PostgreSql数据库对象信息及应用

2022年07月19日144虾米哥

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系统表及视图;通过系统表或视图查询数据库对象及常用统计信息。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/111825822
阅读延展