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

2023-07-07 17:13:03 201

PostgreSql数据库对象主要有数据库、表、视图、索引、schema、函数、触发器等。PostgreSql提供了information_schemaschema，其中包括返回数据库对象的视图。如用户有访问权限，可以也在pg_catalogschema中查询表、视图等对象。

1.查询数据库对象

下面通过示例分别展示如何查询各种数据库对象。

1.1表查询

PostgreSql表信息可以从information_schema.tables或pg_catalog.pg_tables视图中查询：

select*frominformation_schema.tables;
select*frompg_catalog.pg_tables;

1.2查询Schema

获取用户当前选择的schema：

selectcurrent_schema();

返回数据库中所有schema：

select*frominformation_schema.schemata;
select*frompg_catalog.pg_namespace

1.3查询数据库

查询当前选择的数据库：

selectcurrent_database();

返回服务器上所有数据库：

select*frompg_catalog.pg_database

1.4查询视图

查询数据库中所有schema中的所有视图：

select*frominformation_schema.views

select*frompg_catalog.pg_views;

1.5查询表的列信息

查询某个表的列信息：

SELECT
	*
FROM
	information_schema.columns
WHERE
	table_name='employee'
ORDERBY
	ordinal_position;

1.6查询索引信息

查询数据库中所有索引信息；

select*frompg_catalog.pg_indexes;

1.6查询函数信息

返回数据库中所有函数。对于用户定义函数，routine_definition列会有函数体：

select*frominformation_schema.routineswhereroutine_type='FUNCTION';

1.7触发器

查询数据库中所有触发器，action_statemen类别包括触发器body信息：

select*frominformation_schema.triggers;

2.查询表占用空间

2.1查询表占用空间

实际应用中，通常需要表占用磁盘空间情况，我们可以利用系统表实现：

SELECTnspname||'.'||relnameAS"relation",
pg_size_pretty(pg_total_relation_size(C.oid))AS"total_size"
FROMpg_classC
LEFTJOINpg_namespaceNON(N.oid=C.relnamespace)
WHEREnspnameNOTIN('pg_catalog','information_schema')
ANDC.relkind<>'i'
ANDnspname!~'^pg_toast'
ORDERBYpg_total_relation_size(C.oid)DESC
LIMIT5;

示例输出：

relation	total_size
public.snapshots	823MB
public.invoice_items	344MB
public.messages	267MB
public.topics	40MB
public.invoices	35MB

(5rows)

2.2查询数据库占用空间

SELECT
	pg_database.datnameAS"database_name",
	pg_size_pretty(pg_database_size(pg_database.datname))ASsize_in_mb
FROM
	pg_database
ORDERBY
	size_in_mbDESC;

2.3查询表的记录数

可以通过统计系统表进行查询：

SELECTschemaname,relname,n_live_tup
FROMpg_stat_user_tables
ORDERBYn_live_tupDESC
LIMIT12;

顺便说下MySQL对于查询，读者可以对比学习：

SELECTtable_name,table_rows
FROMinformation_schema.tables
WHEREtable_schema=(SELECTdatabase())
ORDERBYtable_rowsDESC
LIMIT12;

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