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

这篇文章主要讲解了“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怎么获取数据库中关键系统信息这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！