Posted by Postgresql 查询表的字段信息，字段名，类型，注释，是否为空
- SELECT
- col_description ( A.attrelid, A.attnum ) AS COMMENT,
- format_type ( A.atttypid, A.atttypmod ) AS TYPE,
- A.attname AS NAME,
- A.attnotnull AS NOTNULL
- FROM
- pg_class AS C,
- pg_attribute AS A
- WHERE
- C.relname = ‘sys_user’
- AND A.attrelid = C.oid
- AND A.attnum > 0
查询所有表名和注释
- SELECT
- relname AS tabname,
- CAST ( obj_description ( relfilenode, ‘pg_class’ ) AS VARCHAR ) AS COMMENT
- FROM
- pg_class C
- WHERE
- relkind = ‘r’
- AND relname NOT LIKE‘pg_%’
- AND relname NOT LIKE‘sql_%’
- ORDER BY
- relname
组合查询
- SELECT
- C.relname AS “表名”,
- CAST ( obj_description ( relfilenode, ‘pg_class’ ) AS VARCHAR ) AS “表描述”,
- A.attname AS “列ID”,
- col_description ( A.attrelid, A.attnum ) AS “列描述”,
- format_type ( A.atttypid, A.atttypmod ) AS “类型及长度”,
- A.attnotnull AS “可否为空”
- FROM
- pg_class AS C,
- pg_attribute AS A
- WHERE
- C.relname IN ( SELECT relname FROM pg_class C WHERE relkind = ‘r’ AND relname NOT LIKE‘pg_%’ AND relname NOT LIKE‘sql_%’ ORDER BY relname )
- AND A.attrelid = C.oid
- AND A.attnum > 0
- AND A.attname NOT LIKE‘%pg.dropped%’
本文链接地址: Postgresql 导出表结构文档