PostgreSql查询数据库中所有表基础信息，以及字段基础信息_col_description

背景

最近项目中需要获取不同类型数据源中所有表信息以及每张表的字段信息：
表名称，注释，所属数据库，所属模式，表占用空间大小
字段名称，注释，类型，长度，是否为主键

mysql中获取这些信息相对简单，postgre中需要使用一些内置函数(主要参考postgre中文手册)

获取表基础信息

SELECT
	"table_name",
	table_catalog,
	table_schema,
	pg_size_pretty ( pg_relation_size ( "table_name" ) ),
	obj_description ( oid, 'pg_class' ) 
FROM
	information_schema.tables t1,
	pg_class t2 
WHERE
	table_schema = 'public' 
	AND t1."table_name" = t2.relname;
1
2
3
4
5
6
7
8
9
10
11
12

以上查询的是当前库中’public’模式下所有表的基础信息，其中表大小用到的两个函数：

表注释使用的函数：

这些函数都可以在手册中进行搜索查看。

获取字段基础信息

SELECT
	base."column_name",
	col_description ( t1.oid, t2.attnum ),
	base.udt_name,
	COALESCE(character_maximum_length, numeric_precision, datetime_precision),
	(CASE
		WHEN ( SELECT t2.attnum = ANY ( conkey ) FROM pg_constraint WHERE conrelid = t1.oid AND contype = 'p' ) = 't' 
		THEN 1 ELSE 0 
	END ) 
FROM
	information_schema.COLUMNS base,
	pg_class t1,
	pg_attribute t2 
WHERE
	base."table_name" = 'sys_org' 
	AND t1.relname = base."table_name" 
	AND t2.attname = base."column_name" 
	AND t1.oid = t2.attrelid 
	AND t2.attnum > 0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

以上查询的是’sys_org’表的字段信息，下面对各个属性进行解释。
使用到的表：


col_description函数在表注释那部分也出现了，这里查询字段注释时需要指定表id(pg_class.oid)以及行号(pg_attribute.attnum)。

COALESCE函数可以理解为mysql中的ifnull，在当前字段为null时展示下一个字段的内容，
这里用到的character_maximum_length, numeric_precision, datetime_precision
分别是字符、数字、时间类型的字段长度。
关于主键这部分相对复杂，单独列出来讲。

判断字段是不是主键

判断字段是否是主键主要用到了pg_constraint这张表

其中contype为约束类型(p为主键约束)，conrelid对应表id(pg_class.oid)，
其中conkey存储的是字段行号数组，如果一张表有多个主键都会存在这一个字段中。
这样我们只需要找到表对应的主键约束信息，通过行号进行关联就行了。
最后使用any函数，判断数据是否包含在数组中。