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怎么獲取數據庫中關鍵系統信息這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！