AnolisOS 8.8 PostgreSQL 获取表字段结构信息

突然接到领导派发的一个需求，现场需要一份当前某个模块下的业务表结构设计说明。需要提供的信息如下：表名、字段名称、字段类型长度、字段备注等信息。

SELECT c.table_name,
       c.column_name,   
       c.data_type,  
       c.character_maximum_length,  
       c.is_nullable,  
       c.column_default,  
       CASE 
         WHEN kcu.column_name IS NOT NULL THEN 'PRIMARY KEY'  
		 ELSE ''  
	   END AS column_key,  
       pgd.description AS column_comment  
FROM information_schema.columns c  
LEFT JOIN information_schema.key_column_usage kcu ON c.table_schema = kcu.table_schema   
    AND c.table_name = kcu.table_name   
    AND c.column_name = kcu.column_name   
LEFT JOIN pg_catalog.pg_description pgd  
    ON pgd.objoid = (  
        SELECT d.oid   
        FROM pg_catalog.pg_class d
        WHERE d.relname = c.table_name   
        AND d.relnamespace = (SELECT n.oid FROM pg_catalog.pg_namespace n WHERE n.nspname = c.table_schema) )  
    AND pgd.objsubid = c.ordinal_position  
WHERE   
    c.table_schema = 'public'  
ORDER BY   
    c.table_name, c.ordinal_position; 

最终的效果如下