亚洲视屏一区,国产电影一区二区,国产精品久久网站

主頁 > 知識庫 > 詳解PostgreSql數據庫對象信息及應用

詳解PostgreSql數據庫對象信息及應用

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	視圖