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

た入场券 2022-11-18 10:59 140阅读 0赞

https://mp.weixin.qq.com/s?\_\_biz=Mzg4NDA0NTEwNA==&mid=2247490085&idx=1&sn=b89c15f10ffa8cc7421f173872cee8fc&chksm=cfbf7e7af8c8f76cf41ac42f09e93d3085a88dd02f697d82c3539fb5b76298f735c2049f015d&scene=178&cur\_album\_id=1507281591604215810\#rd

如何通过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;