PG元数据获取

PG 元数据批量获取 查看库中表信息及注释, 大小 , 行数

SELECT
                --  col.table_catalog
                -- ,col.table_schema
                -- ,col.TABLE_NAME
                -- ,col.ordinal_position
                col.COLUMN_NAME col_name
                ,col.data_type col_type
                -- ,col.character_maximum_length
                -- ,col.numeric_precision
                -- ,col.numeric_scale
                -- ,col.is_nullable
                -- ,col.column_default
                ,case
                    when des.description is null then ''
                    else des.description
                end col_comment
            FROM
                information_schema.COLUMNS col
            LEFT JOIN pg_description des
                ON col.TABLE_NAME::regclass::oid = des.objoid AND col.ordinal_position = des.objsubid
            WHERE
                table_catalog = 'db_name'
            and
                table_schema = 'schema_name'
            and
                TABLE_NAME = 'table_name'
            ORDER BY
                ordinal_position
;

返回结果样式:

 db_name |          table_name           | table_comment | tb_size_mb | table_rows 
---------+-------------------------------+---------------+------------+------------
 public  | ydqtest_cscore_range_37       |               |          0 |          0
 public  | tab_item                      |               |      62190 |  524944896
 public  | ydqtest_cscore_range_38       |               |          0 |          0

获取字段信息及注释

SELECT
                --  col.table_catalog
                -- ,col.table_schema
                -- ,col.TABLE_NAME
                -- ,col.ordinal_position
                col.COLUMN_NAME col_name
                ,col.data_type col_type
                -- ,col.character_maximum_length
                -- ,col.numeric_precision
                -- ,col.numeric_scale
                -- ,col.is_nullable
                -- ,col.column_default
                ,case
                    when des.description is null then ''
                    else des.description
                end col_comment
            FROM
                information_schema.COLUMNS col
            LEFT JOIN pg_description des
                ON col.TABLE_NAME::regclass::oid = des.objoid AND col.ordinal_position = des.objsubid
            WHERE
                table_catalog = 'db_name'
            and
                table_schema = 'schema_name'
            and
                TABLE_NAME = 'table_name'
            ORDER BY
                ordinal_position

返回结果参考:

  col_name   |          col_type           | col_comment 
-------------+-----------------------------+-------------
 iuin        | integer                     | 用户id
 ichangetype | integer                     | 
 dtlogtime   | timestamp without time zone |