Answer a question

I want to for selected table in selected schema get desired result like this

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

notice that some columns don't have description (column comment).

For now i got only this query which gives me good result but only for columns which got comments (for columns that are in the selected table and don't have comment are not represented in output).

MY query which returns only data for columns that have comment is next

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

How to get columns without comment in result?

Answers

because information_schema.columns is the table with data for sure, and you reference it not the first, you need right outer join instead:

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)