postgresql12查询表名、备注及字段、长度、是否可控、是否主键等信息

postgresql12查询表名、备注及字段、长度、是否可控、是否主键等信息

postgresql12查询表名、备注及字段、长度、是否可控、是否主键等信息

• • 获取表名及备注sql
  • 获取指定表的字段名称、长度、是否为空、是否主键等信息

获取表名及备注sql

lname as table_name,d.description as comment
frompg_catalog.pg_class c
join pg_catalog.pg_description d onc.oid = d.objoid
lname = 'tableName'and d.objsubid = 0

获取指定表的字段名称、长度、是否为空、是否主键等信息

lname as 表名,a.attname as 列名,(casewhen a.attnotnull = true then trueelse false end) as 是否非空,(casewhen (selectcount(pg_constraint.*)frompg_constraintinner join pg_class lid = pg_class.oidinner join pg_attribute onpg_attribute.attrelid = pg_class.oidand pg_attribute.attnum = any(key)inner join pg_type onpg_type.oid = pg_attribute.lname = c.relnameand pe = 'p'and pg_attribute.attname = a.attname) > 0 then trueelse false end) as 是否是主键,concat_ws('', t.typname) as 字段类型,(casewhen a.attlen > 0 then a.attlenelse a.atttypmod - 4 end) as 长度,d.description as 备注
frompg_class c,pg_attribute a ,pg_type t,pg_description d
lname = 'your_table_name'and a.attnum>0and a.attrelid = c.oidand a.atttypid = t.oidand d.objoid = a.attrelidand d.objsubid = a.attnum
lname desc,a.attnum asc

其中SQL中的

(casewhen (selectcount(pg_constraint.*)frompg_constraintinner join pg_class lid = pg_class.oidinner join pg_attribute onpg_attribute.attrelid = pg_class.oidand pg_attribute.attnum = any(key)inner join pg_type onpg_type.oid = pg_attribute.lname = c.relnameand pe = 'p'and pg_attribute.attname = a.attname) > 0 then trueelse false end) as 是否是主键

有一个比较特殊，那就是网上很多博客都是创建的单主键表，所以在sql中直接获取pg_catelog库下的pg_constraint表的key的key[1]的值，那么问题来了，有几个主键呢？然后你会发现网上的很多博客都是互相抄，然后就掉坑里出不来了，一张表无论你有几个主键，查询到的结果永远都是一个主键。正确的解决方式应该拿pg_attribute表的pg_attribute.attnum去与pg_constraint中的key数组中做匹配，把匹配的全部拉取出来，就不会遗漏主键了。

附：mysql或mariadb就特别简单了，直接往information_schema表里扒tables表和columns表即可，相对来说，postgresql想找点东西还是挺费劲的