[PostgreSQL - DA] 테이블 스키마 조회 쿼리

맨날 짜서 하다가 귀찮아서 하나 만들어 놨다. 참고 해서 계속 써야지. - 20240102 수정

with schema_column_p as (
select c.table_schema
     , c.table_name
     , obj_description((c.table_schema || '.' || c.table_name)::regclass, 'pg_class') as table_comment
     , c.column_name
     , pgd.description as column_comment
     , (select d.constraint_name as pk
          from information_schema.constraint_column_usage d 
          join information_schema.table_constraints e
            on e.constraint_type = 'PRIMARY KEY'
           and e.table_catalog   = d.table_catalog
           and e.table_schema    = d.table_schema
           and e.table_name      = d.table_name
           and e.constraint_name = d.constraint_name
           and c.table_schema    = d.table_schema
           and c.table_name      = d.table_name
           and c.column_name     = d.column_name)
     , (select tc.constraint_name as fk
          from information_schema.table_constraints tc
          join information_schema.key_column_usage kcu 
            on kcu.constraint_name = tc.constraint_name
           and tc.constraint_type = 'FOREIGN KEY'
          join information_schema.constraint_column_usage ccu 
            on ccu.constraint_name = tc.constraint_name
         where 1=1
           and c.table_schema    = tc.table_schema
           and c.table_name      = tc.table_name
           and c.column_name     = kcu.column_name)
     , column_default
     , is_nullable
     , case when c.udt_name = 'varchar' 
            then concat('varchar(', c.character_maximum_length, ')')
            when c.udt_name = 'numeric'
            then concat('numeric(', c.numeric_precision_radix, ',', numeric_scale, ')') 
            else c.udt_name
        end as "type"
     , c.ordinal_position
     , data_type
     , character_maximum_length as data_length
  from information_schema.columns c
  left join pg_catalog.pg_statio_all_tables as st 
    on c.table_schema = st.schemaname 
   and c.table_name   = st.relname
  left join pg_catalog.pg_description pgd 
    on pgd.objoid = st.relid
   and pgd.objsubid   = c.ordinal_position 
 where 1=1
--   and c.table_schema in ('') -- 추가
   and c.table_schema not like 'pg%'
)
select t01.*
  from schema_column_p t01
 where 1=1
 order by table_schema, table_name, ordinal_position
;

 

+ 추가로 파티션된 테이블을 조회하는 쿼리다. 쓸 일이 있을 듯..

-- 파티션 테이블 조회
select *
  from pg_class pc
  join pg_inherits pi 
    on pc.oid = pi.inhrelid
  join pg_partitioned_table ppt 
    on ppt.partrelid = pi.inhparent
;