189 8069 5689
这篇文章主要讲解了“PostgreSql怎么获取数据库中关键系统信息”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PostgreSql怎么获取数据库中关键系统信息”吧！
创新互联专业为企业提供万宁网站建设、万宁做网站、万宁网站设计、万宁网站制作等企业网站建设、网页设计与制作、万宁企业网站模板建站服务，十载万宁做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。
一般来说每种数据库中都有一个或几个系统的数据库，在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;
感谢各位的阅读，以上就是“PostgreSql怎么获取数据库中关键系统信息”的内容了，经过本文的学习后，相信大家对PostgreSql怎么获取数据库中关键系统信息这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！