《Postgres DBA: 11.查看数据库对象信息》

1. 查看数据库对象

SELECT
          n.nspname as schema_name
        , CASE c.relkind
                    WHEN 'r'
                              THEN 'table'
                    WHEN 'v'
                              THEN 'view'
                    WHEN 'i'
                              THEN 'index'
                    WHEN 'S'
                              THEN 'sequence'
                    WHEN 's'
                              THEN 'special'
          END      as object_type
        , count(1) as object_count
FROM
          pg_catalog.pg_class c
          LEFT JOIN
                    pg_catalog.pg_namespace n
                    ON
                              n.oid = c.relnamespace
WHERE
          c.relkind IN ('r'
                      ,'v'
                      ,'i'
                      ,'S'
                      ,'s')
GROUP BY
          n.nspname
        , CASE c.relkind
                    WHEN 'r'
                              THEN 'table'
                    WHEN 'v'
                              THEN 'view'
                    WHEN 'i'
                              THEN 'index'
                    WHEN 'S'
                              THEN 'sequence'
                    WHEN 's'
                              THEN 'special'
          END
ORDER BY
          n.nspname
        , CASE c.relkind
                    WHEN 'r'
                              THEN 'table'
                    WHEN 'v'
                              THEN 'view'
                    WHEN 'i'
                              THEN 'index'
                    WHEN 'S'
                              THEN 'sequence'
                    WHEN 's'
                              THEN 'special'
          END
;

2. 结果

testdb-# 
    schema_name     | object_type | object_count
--------------------+-------------+--------------
 information_schema | table       |            7
 information_schema | view        |           60
 pg_catalog         | index       |          115
 pg_catalog         | table       |           62
 pg_catalog         | view        |           59
 pg_temp_3          | table       |            1
 pg_toast           | index       |           20
 pg_toast_temp_3    | index       |            1
 public             | table       |            2
(9 rows)