Postgresql 查询表结构信息

qiandeqiande

于 2023-08-18 14:00:36 发布

阅读量645

点赞数

CC 4.0 BY-SA版权

文章标签： postgresql 数据库

本文链接：https://blog.youkuaiyun.com/qiandeqiande/article/details/132360426

本文档提供了一个SQL查询，展示如何从PostgreSQL数据库中获取t_table表的详细结构，包括列名、表名、字段类型和备注信息，按列数顺序排列。

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

select a.attnum AS "序号",
c.relname AS "表名",
cast(obj_description(relfilenode,'pg_class') as varchar) AS "表名描述",
a.attname AS "列名",
concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as "字段类型",
d.description AS "备注"
from pg_class c, pg_attribute a , pg_type t, pg_description d 
where  c.relname = 't_table'
and a.attnum>0 
and a.attrelid = c.oid 
and a.atttypid = t.oid 
and  d.objoid=a.attrelid
and d.objsubid=a.attnum
ORDER BY c.relname DESC,a.attnum ASC ;