postgresql 获取表字段相关属性

最新推荐文章于 2024-04-29 23:30:00 发布

llc的足迹

最新推荐文章于 2024-04-29 23:30:00 发布

阅读量520

点赞数 9

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

本文链接：https://blog.csdn.net/this_is_id/article/details/135774637

版权

数据库专栏收录该内容

20 篇文章 0 订阅

订阅专栏

该SQL查询展示了如何从数据库中提取特定模式和表的列名、描述、类型、长度以及是否允许空值的信息，用于数据表结构分析。

摘要由CSDN通过智能技术生成

select row_number() over ()                                      as 序号,
       t.column_name                                             as 字段名称,
       d.description                                             as 字段说明,
       pt.typname                                                as 字段类型,
       coalesce(character_maximum_length, numeric_precision, -1) as 字段长度,
       case a.attnotnull when true then '是' else '否' end        as 允许空值
from information_schema.columns t,
     pg_attribute a,
     pg_description d,
     pg_class c,
     pg_type pt,
     pg_namespace pn
where d.objoid = a.attrelid
  and d.objsubid = a.attnum
  and a.attname = t.column_name
  and a.attnum > 0
  and a.atttypid = pt.oid
  and a.attrelid = c.oid
  and c.relnamespace = pn."oid"
  and c.relname = t.table_name
  and pn.nspname = t.table_schema
  and t.table_schema = '模式名称'
  and t.table_name = '表名称';