[PostgreSQL] column 정보 조회

PostgreSQL에서 테이블의 컬럼 정보를 조회 하는 쿼리는 아래와 같다.

해당 쿼리는 대상 테이블의 테이블명, 컬럼순서, 컬럼명, 컬럼주석, 데이터타입, 널여부, 디폴트값을 조회하는 Query이다.

 

select UPPER(X2.TABLE_NM) as TABLE_NM -- 테이블명
, X1.ORDINAL_POSITION as COLUMN_NUM -- 컬럼순서
, UPPER(X2.COLUMN_NM) as COLUMN_NM -- 컬럼명
, X2.COLUMN_DESC -- 컬럼주석
, UPPER(
	case 
		when X1.UDT_NAME = 'numeric' and X1.NUMERIC_PRECISION is not null and X1.NUMERIC_SCALE is not NULL then CONCAT(X1.UDT_NAME,'(',X1.NUMERIC_PRECISION,',',X1.NUMERIC_SCALE,')')
		when X1.UDT_NAME in ('varchar', 'bpchar') then CONCAT(X1.UDT_NAME,'(',X1.CHARACTER_MAXIMUM_LENGTH,')')
		else X1.UDT_NAME
	end
) as DATA_TYPE -- 컬럼데이터 타입
, (
	case 
		when X1.IS_NULLABLE = 'NO' then 'N-N'
		else 'NULL'
	end
) as IS_NULLABLE -- not null 여부
, X1.COLUMN_DEFAULT -- 컬럼 디폴트값
from INFORMATION_SCHEMA.columns X1
inner join (
	select T1.SCHEMANAME as SCHEMA_NM
	, T1.RELNAME as TABLE_NM
	, T2.ATTNUM as COLUMN_NUM 
	, T2.ATTNAME as COLUMN_NM
	, T3.DESCRIPTION as COLUMN_DESC
	from PG_CATALOG.PG_STAT_ALL_TABLES T1
	inner join PG_CATALOG.PG_ATTRIBUTE T2 on T1.RELID = T2.ATTRELID and T2.ATTNUM > 0
	left OUTER join PG_CATALOG.PG_DESCRIPTION T3 on T2.ATTRELID = T3.OBJOID and T2.ATTNUM = T3.OBJSUBID
	where t1.SCHEMANAME = '스키마명'
	and t1.RELNAME = LOWER('테이블명')
	order by T2.ATTNUM asc
) X2 on X1.TABLE_SCHEMA = X2.SCHEMA_NM and X1.TABLE_NAME = X2.TABLE_NM and X1.COLUMN_NAME = X2.COLUMN_NM
order by x2.TABLE_NM asc, X1.ORDINAL_POSITION asc
;