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;

示例输出:

relationtotal_size
public.snapshots823 MB
public.invoice_items344 MB
public.messages267 MB
public.topics40 MB
public.invoices35 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系统表及视图;通过系统表或视图查询数据库对象及常用统计信息。

  • 0
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
PostgreSQL是一种支持大小写敏感的标识符的数据库系统,这意味着在创建和引用对象时,大小写必须与其在数据库中存储的方式完全匹配。这有时可能会导致一些问题,因为在不同的平台或环境中有时可能会因大小写不同而无法正常引用对象。解决这个问题的方法如下: 1. 在创建对象时使用统一的命名规则。最好将所有的对象名称转换为小写或大写,这样就可以在所有环境中使用相同的命名约定。例如,在创建表时使用以下命令: CREATE TABLE mytable (...); 2. 引用对象时使用双引号。如果您必须在对象名称中使用大写字母或特殊字符,可以使用双引号将其括起来。例如,如果您必须引用名为"MyTabLE"的表,请使用以下语法: SELECT * FROM "MyTabLE"; 3. 利用PostgreSQL的别名机制。您可以为对象创建别名,从而使其在任何环境中都能正常使用。使用以下语法: CREATE TABLE "mytable" (...); CREATE TABLE "MyTabLE" (...); CREATE TABLE "MYTABLE" (...); CREATE OR REPLACE VIEW MyTable AS SELECT * FROM "MYTABLE"; 在上述例子中,我们在创建视图时引用了完全大写的表名作为别名,从而解决了大小写问题。这样,即使在其他环境中表名的大小写不同,我们仍然可以使用这个别名来访问数据。 通过以上方法,我们可以很好地解决PostgreSQL数据库对象名大小写敏感的问题,使我们的应用程序更具通用性,更易于维护。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值