[PostgreSql]获取表结构数据

PostgreSql的表结构数据存储在pg_stat_user_tables。

pg_stat_user_tables是PostgreSQL中的一个系统表，它记录了用户表的统计信息，比如表的大小、行数、是否在事务中等。

以下是pg_stat_user_tables的一些重要字段及其说明：

relid: 表的OID。

schemaname: 表所属的schema名称。

relname: 表的名称。

seq_scan: 是否通过序列扫描方式访问了表。

seq_tup_read: 通过序列扫描方式读取的行数。

idx_scan: 是否通过索引扫描方式访问了表。

idx_tup_fetch: 通过索引扫描方式获取的行数。

n_tup_ins: 插入到表中的行数。

n_tup_upd: 更新表中的行数。

n_tup_del: 从表中删除的行数。

n_tup_hot_upd: 在表中执行的热更新数（不需要行移动的更新）。

n_live_tup: 表中当前活跃的行数。

n_dead_tup: 表中当前死亡的行数。

vacuum_count: 表被VACUUM操作清理的次数。

n_mod_since_analyze: 自从上次分析操作以来表中变更的行数。

last_vacuum: 表上次被VACUUM操作清理的时间。

last_autovacuum: 表上次被自动VACUUM操作清理的时间。

last_analyze: 表上次被ANALYZE操作更新统计信息的时间。

last_autoanalyze: 表上次被自动ANALYZE操作更新统计信息的时间。

vacuum_segs: 表的VACUUM操作使用的segments数量。

autovacuum_segs: 表的自动VACUUM操作使用的segments数量。

analyze_segs: 表的ANALYZE操作使用的segments数量。

autoanalyze_segs: 表的自动ANALYZE操作使用的segments数量。

 

以下是获取表构成数据用的SQL：

SELECT
    COALESCE(SPLIT_PART(t.COMMENT, ':', 1), c.column_name) AS "注释名"
    , c.column_name AS "列名"
    , ( 
        CASE c.udt_name 
            WHEN 'date' THEN 'DateTime' 
            WHEN 'varchar' THEN 'string' 
            WHEN 'text' THEN 'string' 
            WHEN 'timestamp' THEN 'DateTime' 
            WHEN 'numeric' THEN 'decimal' 
            ELSE ( 
                CASE 
                    WHEN strpos(c.udt_name, 'int') > 0 
                        THEN 'decimal' 
                    ELSE c.udt_name 
                    END
            ) 
            END
    ) AS "类型"
    , ordinal_position AS "行顺序"
    , DENSE_RANK() OVER (ORDER BY c.table_name) AS "表顺序"
    , c.table_name AS "表名" 
FROM
    information_schema.columns c 
    LEFT JOIN ( 
        SELECT
            a.table_name AS table_name
            , a.column_name AS column_name
            , a.data_type AS data_type
            , a.COMMENT AS COMMENT 
        FROM
            ( 
                SELECT
                    pg_stat_user_tables.relname AS table_name
                    , information_schema.columns.column_name AS column_name
                    , information_schema.columns.data_type AS data_type
                    , ( 
                        SELECT
                            description 
                        FROM
                            pg_description 
                        WHERE
                            pg_description.objoid = pg_stat_user_tables.relid 
                            AND pg_description.objsubid = information_schema.columns.ordinal_position
                    ) AS COMMENT 
                FROM
                    pg_stat_user_tables
                    , information_schema.columns 
                WHERE
                    pg_stat_user_tables.relname = information_schema.columns.table_name 
                    AND pg_stat_user_tables.schemaname = current_schema()
            ) AS a 
        WHERE
            a.COMMENT IS NOT NULL
    ) t 
        ON c.table_name = t.table_name 
        AND c.column_name = t.column_name 
WHERE
    table_schema = 'public' -- schema名
ORDER BY
    c.table_name
    , ordinal_position;