postage 查询表结构

一堣而安

已于 2022-09-27 16:11:50 修改

阅读量175

点赞数

分类专栏：数据库文章标签：数据库

于 2022-08-12 16:00:43 首次发布

本文链接：https://blog.csdn.net/oYiErAn/article/details/126305989

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

postage 查询表结构
在这里插入图片描述

select attr.attnum AS "序号",
       pgclass.relname AS "表名",
       obj_description(pgclass.oid) AS "表名描述",
       concat_ws('',pgtype.typname,SUBSTRING(format_type(attr.atttypid,attr.atttypmod) from '\(.*\)')) as "字段类型",
       attr.attname AS "列名",
       descr.description AS "备注",
       (case when attr.attnum = const.conkey[1] then true else false end) "是否主键"
from pg_class pgclass, pg_attribute attr , pg_type pgtype, pg_description descr,pg_constraint const
where  pgclass.relname = '表名'
  and attr.attnum>0
  and attr.attrelid = pgclass.oid
  and attr.atttypid = pgtype.oid
  and descr.objoid=attr.attrelid
  and descr.objsubid=attr.attnum
  and const.conrelid = pgclass.oid

ORDER BY pgclass.relname DESC,attr.attnum ASC