详解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;

示例输出：

视图名	用途
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	视图