PostgreSQL 查询表结构及库表信息

表及注释

SELECT
    n.nspname AS schema_name,         -- 模式名
    c.relname AS table_name,          -- 表名
    obj_description(c.oid) AS table_comment  -- 表备注
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'                 -- r = 普通表
  AND n.nspname NOT IN ('pg_catalog', 'information_schema') -- 排除系统表
ORDER BY n.nspname, c.relname;

表结构查询

SELECT
    a.attnum AS ordinal_position,                 -- 字段顺序
    a.attname AS column_name,                     -- 字段名
    format_type(a.atttypid, a.atttypmod) AS data_type, -- 类型
    CASE 
        WHEN a.attnotnull THEN 'NO' 
        ELSE 'YES' 
    END AS is_nullable,                           -- 是否允许 NULL
    pg_get_expr(ad.adbin, ad.adrelid) AS column_default, -- 默认值
    col_description(a.attrelid, a.attnum) AS column_comment -- 注释
FROM pg_attribute a
LEFT JOIN pg_class c ON a.attrelid = c.oid
LEFT JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_attrdef ad 
       ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
WHERE c.relname = '你的表名'      -- ⚠️ 替换这里
  AND n.nspname = 'public'       -- ⚠️ schema（按需改）
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;