PostgreSQL, get column name, column type and description

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?

just use LEFT JOIN. INNER JOIN gives you an intersection of two tables. If there is no relation from a row in table A to table B, that row won't be included in the output. — KarelG
– KarelG, Commented Jun 12, 2017 at 6:57
tried with left join before posting question and still same result — KuKeC
– KuKeC, Commented Jun 12, 2017 at 6:58

klin · Accepted Answer · 2024-03-15 17:50:06Z

11

There is the function col_description(table_oid, column_number)

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

Test it in db<>fiddle.

edited Mar 15, 2024 at 17:50

answered Jun 12, 2017 at 7:23

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vao Tsun · Accepted Answer · 2017-06-12 07:02:45Z

4

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)

answered Jun 12, 2017 at 7:02

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Comments

Code4R7 · Accepted Answer · 2021-02-05 20:53:09Z

2

Use col_description() on 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;

answered Feb 5, 2021 at 20:53

Code4R7

3,1561 gold badge24 silver badges46 bronze badges

Collectives™ on Stack Overflow

PostgreSQL, get column name, column type and description

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related