PostgreSql 怎么获取数据库中关键系统信息（一）

PostgreSql 怎么获取数据库中关键系统信息（一）

如何通过SQL 的方式获得数据库中的一些关键信息，是一个DB最正常的工作，如何通过一些SQL来获得PG的一些关键的参数和信息或者是数据库中的一些信息是需要知道的一件事情。以下是部分 1

一般来说每种数据库中都有一个或几个系统的数据库，在PG中schemaname 以 pg_catalog开头都是系统表，通过系统表我们就可以了解的大部分数据库系统所做的事情

1  查看当前所有的表（用户表）

SELECT relname

FROM pg_class

WHERE relname !~ '^(pg_|sql_)'

AND relkind = 'r';

或者

也可以

SELECT table_name

FROM information_schema.tables

WHERE table_type = 'BASE TABLE'

AND table_schema NOT IN

('pg_catalog', 'information_schema');

2  查看用户建立的VIEW

SELECT table_name

FROM information_schema.views

WHERE table_schema NOT IN ('pg_catalog', 'information_schema')

AND table_name !~ '^pg_';

3 当前数据库的用户

SELECT usename  FROM pg_user;

4 列出某个表的字段

SELECT a.attname

FROM pg_class c, pg_attribute a, pg_type t

WHERE c.relname = '表名'

AND a.attnum > 0

AND a.attrelid = c.oid

AND a.atttypid = t.oid

5 查询表的索引

SELECT relname, indkey

FROM pg_class, pg_index

WHERE pg_class.oid = pg_index.indexrelid

AND pg_class.oid IN (

SELECT indexrelid

FROM pg_index, pg_class

WHERE pg_class.relname='表名'

AND pg_class.oid=pg_index.indrelid

AND indisunique != 't'

AND indisprimary != 't'

);

查询这个表的那些字段，被建立了索引

SELECT t.relname, a.attname, a.attnum

FROM pg_index c

LEFT JOIN pg_class t

ON c.indrelid  = t.oid

LEFT JOIN pg_attribute a

ON a.attrelid = t.oid

AND a.attnum = ANY(indkey)

WHERE t.relname = '表名'

;

获得当前数据库表的建立索引的语句

SELECT

tablename,

indexname,

indexdef

FROM

pg_indexes

WHERE

schemaname = 'public'

ORDER BY

tablename,

indexname;

6 系统中指定表建立的约束

SELECT constraint_name, constraint_type

FROM information_schema.table_constraints

WHERE table_name = '表名';

7 对某个约束的详细信息的展示

​​SELECT​​​ ​​c.conname ​​​​AS​​​ ​​constraint_name,​​

​​CASE​​​ ​​c.contype​​

​​WHEN​​​ ​​'c'​​​ ​​THEN​​​ ​​'CHECK'​​

​​WHEN​​​ ​​'f'​​​ ​​THEN​​​ ​​'FOREIGN KEY'​​

​​WHEN​​​ ​​'p'​​​ ​​THEN​​​ ​​'PRIMARY KEY'​​

​​WHEN​​​ ​​'u'​​​ ​​THEN​​​ ​​'UNIQUE'​​

​​END​​​ ​​AS​​​ ​​"constraint_type"​​​​,​​

​​CASE​​​ ​​WHEN​​​ ​​c.condeferrable = ​​​​'f'​​​ ​​THEN​​​ ​​0 ​​​​ELSE​​​ ​​1 ​​​​END​​​ ​​AS​​​ ​​is_deferrable,​​

​​CASE​​​ ​​WHEN​​​ ​​c.condeferred = ​​​​'f'​​​ ​​THEN​​​ ​​0 ​​​​ELSE​​​ ​​1 ​​​​END​​​ ​​AS​​​ ​​is_deferred,​​

​​t.relname ​​​​AS​​​ ​​table_name,​​

​​array_to_string(c.conkey, ​​​​' '​​​​) ​​​​AS​​​ ​​constraint_key,​​

​​CASE​​​ ​​confupdtype​​

​​WHEN​​​ ​​'a'​​​ ​​THEN​​​ ​​'NO ACTION'​​

​​WHEN​​​ ​​'r'​​​ ​​THEN​​​ ​​'RESTRICT'​​

​​WHEN​​​ ​​'c'​​​ ​​THEN​​​ ​​'CASCADE'​​

​​WHEN​​​ ​​'n'​​​ ​​THEN​​​ ​​'SET NULL'​​

​​WHEN​​​ ​​'d'​​​ ​​THEN​​​ ​​'SET DEFAULT'​​

​​END​​​ ​​AS​​​ ​​on_update,​​

​​CASE​​​ ​​confdeltype​​

​​WHEN​​​ ​​'a'​​​ ​​THEN​​​ ​​'NO ACTION'​​

​​WHEN​​​ ​​'r'​​​ ​​THEN​​​ ​​'RESTRICT'​​

​​WHEN​​​ ​​'c'​​​ ​​THEN​​​ ​​'CASCADE'​​

​​WHEN​​​ ​​'n'​​​ ​​THEN​​​ ​​'SET NULL'​​

​​WHEN​​​ ​​'d'​​​ ​​THEN​​​ ​​'SET DEFAULT'​​

​​END​​​ ​​AS​​​ ​​on_delete,​​

​​CASE​​​ ​​confmatchtype​​

​​WHEN​​​ ​​'u'​​​ ​​THEN​​​ ​​'UNSPECIFIED'​​

​​WHEN​​​ ​​'f'​​​ ​​THEN​​​ ​​'FULL'​​

​​WHEN​​​ ​​'p'​​​ ​​THEN​​​ ​​'PARTIAL'​​

​​END​​​ ​​AS​​​ ​​match_type,​​

​​t2.relname ​​​​AS​​​ ​​references_table,​​

​​array_to_string(c.confkey, ​​​​' '​​​​) ​​​​AS​​​ ​​fk_constraint_key​​

​​FROM​​​ ​​pg_constraint c​​

​​LEFT​​​ ​​JOIN​​​ ​​pg_class t  ​​​​ON​​​ ​​c.conrelid  = t.oid​​

​​LEFT​​​ ​​JOIN​​​ ​​pg_class t2 ​​​​ON​​​ ​​c.confrelid = t2.oid​​

​​WHERE​​​ ​​t.relname = ​​​​'表名'​​

​​AND​​​ ​​c.conname = ​​​​'约束名'​​​​;​​

8 列出相关的自增序列

SELECT relname

FROM pg_class

WHERE relkind = 'S'

AND relnamespace IN (

SELECT oid

FROM pg_namespace

WHERE nspname NOT LIKE 'pg_%'

AND nspname != 'information_schema'

);

9 筛选相关数据库中建立的trigger

​​SELECT​​​ ​​DISTINCT​​​ ​​trigger_name​​

​​FROM​​​ ​​information_schema.triggers​​

​​WHERE​​​ ​​trigger_schema ​​​​NOT​​​ ​​IN​​

​​(​​​​'pg_catalog'​​​​, ​​​​'information_schema'​​​​);​​

以及关于 trigger 详细的信息

SELECT *

FROM information_schema.triggers

WHERE trigger_schema NOT IN

('pg_catalog', 'information_schema');

10 查看系统中创建的函数

SELECT routine_name

FROM information_schema.routines

WHERE specific_schema NOT IN

('pg_catalog', 'information_schema')

AND type_udt_name != 'trigger';

11 查看当前数据库中表的主键

SELECT tc.constraint_name,

tc.constraint_type,

tc.table_name,

kcu.column_name,

tc.is_deferrable,

tc.initially_deferred,

rc.match_option AS match_type,

rc.update_rule AS on_update,

rc.delete_rule AS on_delete,

ccu.table_name AS references_table,

ccu.column_name AS references_field

FROM information_schema.table_constraints tc

LEFT JOIN information_schema.key_column_usage kcu

ON tc.constraint_catalog = kcu.constraint_catalog

AND tc.constraint_schema = kcu.constraint_schema

AND tc.constraint_name = kcu.constraint_name

LEFT JOIN information_schema.referential_constraints rc

ON tc.constraint_catalog = rc.constraint_catalog

AND tc.constraint_schema = rc.constraint_schema

AND tc.constraint_name = rc.constraint_name

LEFT JOIN information_schema.constraint_column_usage ccu

ON rc.unique_constraint_catalog = ccu.constraint_catalog

AND rc.unique_constraint_schema = ccu.constraint_schema

AND rc.unique_constraint_name = ccu.constraint_name

WHERE tc.table_name !~ '^(pg_|sql_)'

AND tc.constraint_type = 'PRIMARY KEY';

12  获得索引与表之间的关系

SELECT a.index_name, b.attname

FROM (

SELECT a.indrelid,

c.relname index_name,

unnest(a.indkey) index_num

FROM pg_index a,

pg_class b,

pg_class c

WHERE

b.oid=a.indrelid

AND a.indisprimary != 't'

AND a.indexrelid=c.oid

) a,

pg_attribute b

WHERE a.indrelid = b.attrelid

AND a.index_num = b.attnum and a.index_name !~ '^(pg_|sql_)'

ORDER BY a.index_name, a.index_num

13  显示VIEW 之间的依赖关系

SELECT v.relname AS "dependent_view",

t.relname AS "referenced_relation"

FROM pg_depend dv

LEFT JOIN pg_class v ON v.oid = dv.refobjid

LEFT JOIN pg_namespace nv ON v.relnamespace = nv.oid

LEFT JOIN pg_depend dt

ON dv.classid = dt.classid

AND dv.objid = dt.objid

AND dv.refobjid <> dt.refobjid

AND dv.refclassid = dt.refclassid

AND dv.classid = 'pg_catalog.pg_rewrite'::regclass

AND dv.refclassid = 'pg_catalog.pg_class'::regclass

LEFT JOIN pg_class t ON t.oid = dt.refobjid

LEFT JOIN pg_namespace nt

ON t.relnamespace = nt.oid

AND nv.nspname = 'public'

AND nt.nspname = 'public'

WHERE dv.deptype = 'i'

AND v.relkind = 'v'

AND t.relkind IN ('r', 'v')

AND v.relname = 'testttt' -- VIEW NAME

GROUP BY v.relname, t.relname;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。