postgresql12查询表名、备注及字段、长度、是否可控、是否主键等信息
备注:sql确定有效版本为pgsql12,其他版本未测试,网上很多博客的查询方式以及存在很大问题
获取表名及备注sql
select
c.relname as table_name,
d.description as comment
from
pg_catalog.pg_class c
join pg_catalog.pg_description d on
c.oid = d.objoid
where
c.relname = 'tableName'
and d.objsubid = 0
获取指定表的字段名称、长度、是否为空、是否主键等信息
select
c.relname as 表名,
a.attname as 列名,
(case
when a.attnotnull = true then true
else false end) as 是否非空,
(case
when (
select
count(pg_constraint.*)
from
pg_constraint
inner join pg_class on
pg_constraint.conrelid = pg_class.oid
inner join pg_attribute on
pg_attribute.attrelid = pg_class.oid
and pg_attribute.attnum = any(pg_constraint.conkey)
inner join pg_type on
pg_type.oid = pg_attribute.atttypid
where
pg_class.relname = c.relname
and pg_constraint.contype = 'p'
and pg_attribute.attname = a.attname) > 0 then true
else false end) as 是否是主键,
concat_ws('', t.typname) as 字段类型,
(case
when a.attlen > 0 then a.attlen
else a.atttypmod - 4 end) as 长度,
d.description as 备注
from
pg_class c,
pg_attribute a ,
pg_type t,
pg_description d
where
c.relname = 'your_table_name'
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
其中SQL中的
(case
when (
select
count(pg_constraint.*)
from
pg_constraint
inner join pg_class on
pg_constraint.conrelid = pg_class.oid
inner join pg_attribute on
pg_attribute.attrelid = pg_class.oid
and pg_attribute.attnum = any(pg_constraint.conkey)
inner join pg_type on
pg_type.oid = pg_attribute.atttypid
where
pg_class.relname = c.relname
and pg_constraint.contype = 'p'
and pg_attribute.attname = a.attname) > 0 then true
else false end) as 是否是主键
有一个比较特殊,那就是网上很多博客都是创建的单主键表,所以在sql中直接获取pg_catelog库下的pg_constraint表的pg_constraint.conkey的pg_constraint.conkey[1]的值,那么问题来了,有几个主键呢?然后你会发现网上的很多博客都是互相抄,然后就掉坑里出不来了,一张表无论你有几个主键,查询到的结果永远都是一个主键。正确的解决方式应该拿pg_attribute表的pg_attribute.attnum去与pg_constraint中的pg_constraint.conkey数组中做匹配,把匹配的全部拉取出来,就不会遗漏主键了。
附:mysql或mariadb就特别简单了,直接往information_schema表里扒tables表和columns表即可,相对来说,postgresql想找点东西还是挺费劲的