PostgreSQL，获取列名、列类型和描述

Question

我想为所选模式中的所选表获得这样的期望结果

column_name  |  data_type   | description
-----------------------------------------
id           | integer      |  id of bill
name         | character    |
address      | character    | adress of buyer

请注意，某些列没有描述(列注释)。

现在我只得到这个查询，它给我很好的结果，但只针对有评论的列(对于所选表中没有评论的列，输出中不表示)。

下一个是我的查询，它只返回有评论的列的数据

SELECT c.column_name, c.data_type, pgd.description 
from pg_catalog.pg_statio_all_tables as st 
inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid) 
inner join information_schema.columns c on (pgd.objsubid=c.ordinal_position and  c.table_schema=st.schemaname and c.table_name=st.relname)  
where table_schema = 'public' and table_name = 'some_table';

如何获取结果中没有注释的列？

Answer 1

函数col_description(table_oid, column_number)

select column_name, data_type, col_description('public.my_table'::regclass, ordinal_position)
from information_schema.columns
where table_schema = 'public' and table_name = 'my_table';

 column_name | data_type |  col_description  
-------------+-----------+-------------------
 gid         | integer   | a comment for gid
 id          | integer   | 
 max_height  | numeric   | 
(3 rows)