pgsql 查询表字段，类型，备注

TaLinBoy

已于 2023-03-28 10:49:38 修改

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 postgresql

于 2023-03-28 10:46:30 首次发布

本文链接：https://blog.csdn.net/TaLinBoy/article/details/129811405

博客给出了一段PostgreSQL的SQL代码，用于查询指定表的字段信息。代码通过连接多个系统表和信息模式表，获取表名、字段序号、字段描述、字段名、数据类型等信息，并按字段序号排序。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT
B.relname "table",
A.attnum,
( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS descript,
A.attname as "column",
pg_catalog.format_type ( A.atttypid, A.atttypmod ) AS data_type,
C.numeric_precision as len,
C.udt_name as "type"
FROM
pg_catalog.pg_attribute A
LEFT JOIN pg_class B on A.attrelid = B.OID
LEFT JOIN information_schema.COLUMNS as C on C."table_name" = B.relname AND  A.attname = C.COLUMN_NAME and C.table_catalog = '${dbName}'
WHERE
B.relname ='${tableName}'
AND A.attnum > 0
AND NOT A.attisdropped
ORDER BY
A.attnum;