列出表名

SELECT tablename from pg_tables
WHERE tablename not like 'pg%'
and tablename not like 'sql_%'
order by tablename ;

image.png

• 排除postgresql 中的系统表 按表名排序

列出字段类型和名称

SELECT
    format_type (A .atttypid, A .atttypmod) AS TYPE,
    A .attname AS NAME
FROM
    pg_class AS C,
    pg_attribute AS A
WHERE
    C .relname = 'your tablename'
AND A .attrelid = C .oid
AND A .attnum > 0;

image.png

• atttypid 字段类型对应id
• atttypmod 字段类型设置
• format_type 获取一个数据类型的SQL名称
• pg_class 所有表的信息。 oid是relname对应的表的唯一id
• pg_attribute 所有属性的信息 attrelid 是 这条属性对应的表的id