PostgreSQL数据库系统表查询

以下是使用 PostgreSQL 查询所有模式、表名、表注释、字段数量、数据条目数及容量大小的完整方案：

​​一、查询所有模式​​

SELECT nspname AS schema_name
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema';

• ​​说明​​：通过系统表 pg_namespace 获取所有用户自定义模式，排除系统模式。

 

​​二、查询所有表及其元数据​​

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    obj_description(c.oid) AS table_comment,
    (SELECT COUNT(*) FROM pg_attribute a  WHERE a.attrelid = c.oid AND a.attnum > 0) AS column_count,  -- 字段数量
    c.reltuples::BIGINT AS estimated_row_count,  -- 记录数
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM    pg_class c
JOIN    pg_namespace n ON c.relnamespace = n.oid
WHERE    c.relkind = 'r'  -- 仅普通表
    AND n.nspname NOT IN ('pg_catalog', 'information_schema');

• ​​字段说明​​：
• ​​schema_name​​：表所属模式。
• ​​table_name​​：表名称。
• ​​table_comment​​：通过 obj_description 函数获取表注释。
• ​​column_count​​：统计 pg_attribute 中的字段数量。
• ​​estimated_row_count​​：基于统计信息的估算行数（需定期执行 ANALYZE 更新）。
• ​​total_size​​：表总大小（含索引），使用 pg_total_relation_size 函数。

 

查询表、字段名称、字段注释、数据类型：

SELECT
    t.table_schema AS 模式名,
    t.table_name AS 表名,
    obj_description(pg_class.oid, 'pg_class') AS 表注释,
    c.column_name AS 字段名,
    pgd.description AS 字段注释,
    c.data_type AS 数据类型,
    c.character_maximum_length AS 字符最大长度,
    c.numeric_precision AS 数字精度,
    c.numeric_scale AS 小数位数,
    c.is_nullable AS 是否可为空
FROM
    information_schema.tables t
JOIN
    information_schema.columns c
    ON t.table_schema = c.table_schema AND t.table_name = c.table_name
JOIN
    pg_class ON pg_class.relname = t.table_name
JOIN
    pg_namespace ON pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = t.table_schema
LEFT JOIN
    pg_description pgd ON pgd.objsubid = c.ordinal_position AND pgd.objoid = pg_class.oid
WHERE
    t.table_type = 'BASE TABLE'
    and t.table_schema = 'middle'  -- 模式名
    and t.table_name = 't_fact_bcg_goods' -- 表名
ORDER BY
    t.table_name, c.ordinal_position;

 

SELECT
    c.ordinal_position AS 序号,
    c.column_name AS 字段名,
    pgd.description AS 字段注释,
    c.data_type AS  长数据类型,
    c.udt_name  AS  短数据类型,
    pg_catalog.format_type(pa.atttypid, pa.atttypmod) AS 格式化长数据类型,
    REPLACE(REPLACE(pg_catalog.format_type(pa.atttypid, pa.atttypmod),'(0)',''),c.data_type,c.udt_name)  AS 格式化短数据类型,
    IF(c.is_nullable='NO','√',NULL) AS 是否可为空
FROM
    information_schema.columns c
JOIN
    pg_class  pc  ON pc.relname = c.table_name
JOIN  
    pg_attribute  pa ON pa.attrelid = pc.oid  AND  pa.attname = c.column_name 
JOIN
    pg_namespace  pn  ON pn.oid = pc.relnamespace AND pn.nspname = c.table_schema
LEFT JOIN
    pg_description pgd  ON pgd.objsubid = c.ordinal_position AND pgd.objoid = pc.oid
WHERE
     c.table_schema = 'schema_name'  -- 模式名
    and c.table_name = 'table_name' -- 表名
    AND pa.attnum > 0 AND NOT pa.attisdropped
ORDER BY
    c.table_name, c.ordinal_position;

 

 

​​三、扩展：精确行数与字段详情​​

1. ​​精确行数（适用于小表）​​

SELECT 
schemaname, 
relname, 
COUNT(*) AS exact_row_count
FROM pg_stat_user_tables
GROUP BY schemaname, relname;

• ​​说明​​：pg_stat_user_tables 提供实时行数，但需全表扫描。

 

2. ​​字段详情（含注释与类型）​​

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
    col_description(a.attrelid, a.attnum) AS column_comment
FROM    pg_attribute a
JOIN    pg_class c ON a.attrelid = c.oid
JOIN    pg_namespace n ON c.relnamespace = n.oid
WHERE    a.attnum > 0 AND NOT a.attisdropped;

• ​​说明​​：查询字段名称、类型及注释。

 

​​四、批量查询所有表的存储信息​​

SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_table_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM    pg_stat_user_tables
ORDER BY   total_size DESC;

• ​​说明​​：统计表数据、索引及总大小，适用于存储优化分析。

 

​​五、注意事项​​

1. ​​权限要求​​：需对系统表（如 pg_class、pg_namespace）有查询权限。
2. ​​统计信息更新​​：使用 ANALYZE 命令更新 reltuples 的准确性。
3. ​​性能优化​​：估算行数（reltuples）适合快速统计，精确行数（COUNT(*)）适用于小表。