Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

SELECT c.table_schema,c.table_name,c.column_name,
case 
    when c.domain_name is not null then c.domain_name
    when c.data_type='character varying' THEN 'character varying('||c.character_maximum_length||')'
    when c.data_type='character' THEN 'character('||c.character_maximum_length||')'	
    when c.data_type='numeric' THEN 'numeric('||c.numeric_precision||','||c.numeric_scale||')'
    else c.data_type
end as data_type,
c.is_nullable, 
(select 'Y' from information_schema.table_constraints tco
join information_schema.key_column_usage kcu 
     on kcu.constraint_name = tco.constraint_name
     and kcu.constraint_schema = tco.constraint_schema
     and kcu.constraint_schema = c.table_schema
 and kcu.table_name = c.table_name
 and kcu.column_name = c.column_name
where tco.constraint_type = 'PRIMARY KEY' ) as is_in_PK,
(select distinct 'Y' from information_schema.table_constraints tco
join information_schema.key_column_usage kcu 
     on kcu.constraint_name = tco.constraint_name
     and kcu.constraint_schema = tco.constraint_schema
     and kcu.constraint_schema = c.table_schema
 and kcu.table_name = c.table_name
 and kcu.column_name = c.column_name
where tco.constraint_type = 'FOREIGN KEY' ) as is_in_FK,
pgd.description

FROM pg_catalog.pg_statio_all_tables as st
  Left outer join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
  left 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 c.table_schema='public' 
	--and 
where	c.table_name='test'
order by c.table_schema,c.table_name,c.ordinal_position;

table_schema	table_name	column_name	data_type	is_nullable	is_in_pk	is_in_fk	description
public	test	id	integer	YES	null	null	test dicr