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想找点东西还是挺费劲的