[PostgreSQL] 다양한 Meta 정보를 읽는 방법들 기록

테이블 컬럼 정보조회

여러 사이트를 돌아다니면서 조금조금씩 추가하면서 만들어본 쿼리입니다.
이해하시려면 약간의 분석시간이 필요합니다!
솔직히 저도 다시 보니까 기억이 안나네요 😂

조회되는 정보는 아래와 같습니다.

• schema_nm : 스키마 명
• table_nm : 테이블 명
• table_description : 테이블 주석
• column_nm : 컬럼 명
• column_type : 컬럼 타입
• length_of_col : 컬럼 길이 (문자열이면 max_length, numeric 이면 precision)
• column_type_detail : 컬럼 타입(컬럼 길이) 포맷의 문자열
• column_description : 컬럼 주석
• is_not_null : not null 여부, ('Y', 'N') 문자열 반환
• pk_num : pk 넘버링, pk 가 여러개면 PK01, PK02 ... 처럼 출력
• foreign_table_name : foreign key 일 때 참조하는 테이블
• foreign_column_name : foreign key 일 때 참조하는 테이블의 컬럼명
• foreign_key_ref_info : foreign key 정보를 이쁘게 출력
• order_of_column : 컬럼의 순서

SELECT  lower(n.nspname) as schema_nm,
        lower(c.relname) as table_nm,
		obj_description(c.oid) as table_description,
        a.attname as column_nm,
        a.atttypid::regtype as column_type,
		coalesce(ii.character_maximum_length, ii.numeric_precision) as length_of_col,
        format_type(a.atttypid, a.atttypmod) as column_type_detail,
	    d.description as column_description,
        case
            when a.attnotnull then 'Y'
            else 'N'
        end is_not_null,
        case
            when a.attnum = ANY (i.indkey) then 'PK' || to_char(a.attnum, 'fm00')
            else ''
        end as pk_num, -- PK 정보. PK 가 여러개면 PK01, PK02 처럼 출력
       cc.foreign_table_name, -- 외래키 관련 정보(1)
       cc.foreign_column_name, -- 외래키 관련 정보(2)
       substring(cc.foreign_table_name::text, '[^.]*$') 
       	 || '.' ||
         cc.foreign_column_name as foreign_key_ref_info, -- 이쁘게 출력
        a.attnum as order_of_column
FROM pg_catalog.pg_attribute AS a
         JOIN pg_catalog.pg_class AS c ON a.attrelid = c.oid
         LEFT JOIN pg_catalog.pg_description AS d 
         		ON c.oid = d.objoid AND a.attnum = d.objsubid
         JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid
         join information_schema.columns as ii
         	on lower(ii.table_schema) = lower(n.nspname) 
            	and lower(ii.table_name) = lower(c.relname)
                and lower(ii.column_name) = lower(a.attname)
		 left join pg_index AS i 
         	ON c.oid = i.indrelid AND i.indisprimary -- pk 가 없을 수도 있어서 left join!
         left join -- 참조키는 없을 수도 있다! left join!
            (SELECT 
            	conrelid, 
                conname AS constraint_name, 
                conrelid::regclass AS table_name, 
                ta.attname AS column_name,
				confrelid::regclass AS foreign_table_name, 
                fa.attname AS foreign_column_name
              FROM (
                  SELECT conname, conrelid, confrelid,
                        unnest(conkey) AS conkey, 
                        unnest(confkey) AS confkey
                  FROM pg_constraint
               ) sub
               JOIN pg_attribute AS ta ON ta.attrelid = conrelid AND ta.attnum = conkey
               JOIN pg_attribute AS fa ON fa.attrelid = confrelid AND fa.attnum = confkey
            ) as cc
            on cc.conrelid = c.oid and cc.column_name = a.attname
WHERE lower(n.nspname) = '스키마 명칭 입력'  -- 테이블 스키마명을 변경해야 할 수도 있습니다.
  		AND lower(c.relname) = '테이블 명칭 입력' -- 조회하려는 테이블명을 입력합니다.
  		AND a.attnum > 0 -- attnum=0 인 row 는 테이블 자체에 대한 정보다. 0 이상이 컬럼 정보
    	AND NOT a.attisdropped
    	AND c.relkind = 'r' -- 일반 테이블의 컬럼만 조회
ORDER BY c.relname, a.attnum;

• AND lower(c.relname) = 'table_name' 를 주석처리하면 해당 스키마에 있는
  모든 테이블의 컬럼정보를 조회할 수 있다.

생성한 인덱스 목록조회

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
	schemaname = 'schema_name'
	and tablename LIKE 'table_name'
ORDER BY
    tablename,
    indexname;



-- 한 테이블에 인덱스가 여러개 있는 경우에는
-- 아래처럼 하면 더 보기 좋습니다.
with tt(schema_name, table_name) as (values
	('user','user_info'),
	('auth','auth_manage')
)
SELECT
    tablename,
    concat(tablename, ' -> ' , array_agg(indexname))
FROM pg_indexes a
inner join tt b 
	on  a.schemaname = b.schema_name and  a.tablename = b.table_name
group by tablename
ORDER BY
    tablename;

object 사이즈, 타입 조회

select
       pg_size_pretty(pg_relation_size(oid)) as pretty_size,
       relname,
       case
           when relkind = 'r' then '일반 테이블'
           when relkind = 'i' then '인덱스'
           when relkind = 'S' then '시퀀스'
           when relkind = 'v' then '뷰'
           when relkind = 'm' then '마테리얼라이즈드 뷰'
           when relkind = 'c' then '컴파일된 테이블 함수'
           when relkind = 'f' then '컴파일된 외부 테이블 함수'
           when relkind = 'p' then '파티션'
           when relkind = 'I' then '퍼지 인덱스'
           when relkind = 's' then '토스트 테이블'
           when relkind = 't' then 'TOAST 인덱스'
           end reltype_nm,
       relispartition -- 파티션 테이블 여부
from pg_class
order by pg_relation_size(oid) desc;