Postgresql DB 테이블 정보 조회

오라클, mysql, MS-SQL, Postgresql등의 DB에서는 DB의 테이블 및 컬럼정보를

제공하는 방법이 있습니다.

여기서는 Postgresql을 기준으로 알아볼게요

 

1. 테스트 테이블생성

테스트할 테이블을 먼저 생성합니다.

-- Drop table

-- DROP TABLE rdm.tb_table_info;

CREATE TABLE rdm.tb_table_info (
	column1 varchar(20) NOT NULL, -- 컬럼1
	column2 varchar(100) NULL, -- 컬럼2
	column3 varchar(100) NULL, -- 컬럼3
	column4 varchar(100) NULL, -- 컬럼4
	column5 varchar(100) NULL, -- 컬럼5
	column6 varchar(100) NULL, -- 컬럼6
	column7 varchar(100) NULL, -- 컬럼7
	regist_dt timestamp NOT NULL, -- 등록일시
	CONSTRAINT tb_table_info_pk PRIMARY KEY (column1)
);

-- Column comments

COMMENT ON COLUMN rdm.tb_table_info.column1 IS '컬럼1';
COMMENT ON COLUMN rdm.tb_table_info.column2 IS '컬럼2';
COMMENT ON COLUMN rdm.tb_table_info.column3 IS '컬럼3';
COMMENT ON COLUMN rdm.tb_table_info.column4 IS '컬럼4';
COMMENT ON COLUMN rdm.tb_table_info.column5 IS '컬럼5';
COMMENT ON COLUMN rdm.tb_table_info.column6 IS '컬럼6';
COMMENT ON COLUMN rdm.tb_table_info.column7 IS '컬럼7';
COMMENT ON COLUMN rdm.tb_table_info.regist_dt IS '등록일시';

-- Permissions

ALTER TABLE rdm.tb_table_info OWNER TO dev;
GRANT ALL ON TABLE rdm.tb_table_info TO dev;

2. Postgresql 스키마 정보조회

--------------------------------------------------
--1. 스키마 정보 가져오기
--------------------------------------------------
select
	*
from pg_namespace
where nspname='rdm'
order by 1

결과입니다.

3. Postgresql 테이블 정보 조회

Postgresql에서는 2가지 방법이 있어요

 

1) 전체 테이블정보에서 가져오기

2) 사용자 정보테이블에서 가져오기

 

postgresql 테이블정보 조회

 

4. Postgresql 컬럼 정보 , Description 정보조회

Postgresql에서는 컬럼정보와 Description정보를 각각 가져오셔야해요.

 

--테이블 및 컬럼의 Desription정보

select * from PG_DESCRIPTION 

--컬럼의 속성정보

select * from PG_ATTRIBUTE 

--컬럼데이터타입 정보

select * from INFORMATION_SCHEMA.COLUMNS

 

5. 앞에 정보들을 조합해서 Postgresql 테이블, 컬럼 특성정보 조회

--------------------------------------------------
--5. 테이블 및 컬럼정보 가져오기
--------------------------------------------------
select
	tb.schemaname as 스키마명
	,tb.relname as 테이블명
	, tb_dc.description as 테이블설명
	, col.attname as 컬럼명
	, col.attnum as 컬럼순서
	, col_dc.description as 컬럼설명
	, col_type.data_type as 컬럼데이터타입
	, col_type.character_maximum_length as len
from
(
	select * from PG_STAT_ALL_TABLES
	where 1=1
	and RELNAME = 'tb_table_info'
	and schemaname='rdm'
) tb--테이블정보
left join PG_DESCRIPTION tb_dc--테이블설명
on tb_dc.OBJSUBID = 0 and tb.RELID = tb_dc.OBJOID
left join PG_ATTRIBUTE col--컬럼정보
on tb.relid=col.ATTRELID
left join PG_DESCRIPTION col_dc--컬럼설명
on col_dc.OBJSUBID <> 0 and tb.RELID = col_dc.OBJOID and col_dc.OBJOID = col.ATTRELID and col_dc.OBJSUBID = col.ATTNUM
left join INFORMATION_SCHEMA.COLUMNS col_type--컬럼데이터타입
on col_type.table_schema=tb.schemaname and col_type.table_name=tb.relname and col_type.column_name=col.attname and col_type.ordinal_position=col.attnum
where 1=1
and col.attstattarget ='-1'
order by col.attnum

결과입니다.