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';

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

- KuKeC

---

只需使用 LEFT JOIN。INNER JOIN 给出两个表的交集。如果从表 A 中的某一行到表 B 没有关系，则该行不会包含在输出中。 - KarelG

在发帖之前尝试过左连接，但结果仍然相同。 - KuKeC

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)

- klin

Answer 2

因为 information_schema.columns 是包含数据的表，而且你不是第一次引用它，所以你需要使用 right outer join：

t=# 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)
right outer 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 = 's150';
  column_name  | data_type | description
---------------+-----------+-------------
 customer_name | text      |
 order_id      | text      |
 order_date    | date      |
(3 rows)

- Vao Tsun

Answer 3

使用col_description()函数，针对pg_attribute表进行操作：

select
  attname as column_name,
  atttypid::regtype as data_type,
  col_description(attrelid, attnum) as description
from pg_attribute
where attrelid = '(myschema.)mytable'::regclass
  and attnum > 0        -- hide system columns
  and not attisdropped  -- hide dead/dropped columns
order by attnum;

- Code4R7