[postgres] table, column info

스키마 정보 조회

select 
  a.relname
, b.description
, b.description
, d.attname
, c.description
--, e.data_type 
, e.udt_name
, case
    when e.data_type = 'character varying' then text(e.character_maximum_length)
    when e.data_type = 'numeric' then text(e.numeric_precision) || ',' || text(e.numeric_scale)
    when e.data_type = 'bigint' then  '8' --text(e.numeric_precision)
    when e.data_type = 'integer' then '4' --text(e.numeric_precision)
    when e.data_type = 'bit' then text(e.character_maximum_length)
    when e.data_type = 'timestamp without time zone' then '8' --text(e.datetime_precision)
    when e.data_type = 'date' then '8' 
    when e.data_type = 'text' then '65535' end as type_size
, case when f.column_name is not null then 'Y' else 'N' end
, case when e.is_nullable = 'YES' then 'Y' else 'N' end
--, case when d.attnotnull = true then 'Y' else 'N' end
, e.column_default 
, case when e.udt_name = 'date' then 'YYYYMMDD'
       when e.udt_name = 'timestamp' then 'YYYYMMDDHH24MISS'
       else '' end as format
, c.description
from  PG_STAT_ALL_TABLES a
left outer join PG_DESCRIPTION b on b.objoid = a.relid and b.objsubid = 0
left outer join PG_DESCRIPTION c on c.objoid = a.relid 
left outer join PG_ATTRIBUTE d on d.attrelid = c.objoid and d.attnum = c.objsubid
left outer join information_schema.columns e on e.table_name = a.relname and  e.column_name = d.attname
left outer join ( -- pk find               
	select a.constraint_schema
	       , a.constraint_name
	       , b.table_name
	       , b.column_name	       
	from    information_schema.table_constraints a
	left outer join information_schema.key_column_usage b on b.constraint_name = a.constraint_name
					and b.constraint_schema = a.constraint_schema
					and b.constraint_name = a.constraint_name
	where a.constraint_type = 'PRIMARY KEY' 
) f on f.table_name = a.relname and f.column_name = d.attname
where a.SCHEMANAME = 'public' 
and d.attname is not null
order by a.relname, d.attname asc