[PostgreSQL] table, column, column 속성, description 정보 select 하기

일하다가 모든 table에 대한 column과 description을 정리해야 할 일이 생겼는데

postgreSQL이라 그런건지 구글링을 덜한건지 내가 원하는걸 딱 못찾아서

구글링한걸 바탕으로 쿼리를 짰다.

아직 좀 수정해야할 부분이 있지만 일단 올려두고 고치든지 해야겠다.

더 깔끔하게 짜고싶은데 능력부족ㅠ

수정 다 해두었으니 필요한 부분만 선택해서 쓰면 될 듯!

 

밑줄친 부분에 알맞는 값을 넣어서 쿼리를 실행하면 된다.

 

-- table 목록 조회 쿼리 --

SELECT *
FROM information_schema.tables
WHERE table_schema = '스키마 이름' AND table_type = 'BASE TABLE';

 

-- table description 조회 쿼리 --

SELECT description
  FROM pg_catalog.pg_description
 WHERE objoid = ( SELECT oid 
                    FROM pg_class 
                   WHERE relname = '테이블 이름'
                     AND relnamespace = ( SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = '스키마 이름' ) )
   AND objsubid = 0;

 

-- table column과 description 조회 쿼리 --

SELECT b.column_name, a.description
FROM ( select *
       from pg_catalog.pg_description 
       where objoid = ( SELECT oid 
                        FROM pg_class 
                        WHERE relname = '테이블 이름' 
                        AND relnamespace = ( SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = '스키마 이름' ) ) ) AS a
RIGHT JOIN (SELECT *
            FROM information_schema.columns
            WHERE table_schema = '스키마 이름'
            AND table_name = '테이블 이름') b
ON a.objsubid = b.ordinal_position;

 

-- table_name, column_name, description, data_type, max_length, nullabe, pk 조회 쿼리 (column_name이 null인건 table description)

-- 첫 번째 RIGHT JOIN은 테이블과 그 테이블의 컬럼들의 description을 가져오기 위한 JOIN
-- 두 번째 FULL OUTER JOIN은 컬럼들의 속성(data_type, max_length 등등)을 가져오기 위한 JOIN
-- 세 번째 LEFT OUTER JOIN은 컬럼의 PK 여부를 가져오기 위한 JOIN
-- 필요한 JOIN만 사용해서 원하는 정보를 select하면 됨
SELECT nvl(b.relname, c.table_name) AS table_name, c.column_name, a.description, c.data_type, c.character_maximum_length, c.is_nullable,
 CASE nvl(d.constraint_name, '') WHEN '' THEN NULL ELSE 'PK' END is_pk
FROM pg_catalog.pg_description a
 RIGHT JOIN 
     ( SELECT oid, * 
 FROM pg_class 
 WHERE relname IN (SELECT table_name
 FROM information_schema.tables
 WHERE table_schema = '스키마 이름' AND table_type = 'BASE TABLE')
 AND relnamespace = ( SELECT oid FROM pg_catalog.pg_namespace WHERE nspname =  ) ) b
 ON a.objoid = b.oid
 FULL OUTER JOIN
 ( SELECT tab.table_name, col.column_name, col.ordinal_position, col.data_type, col.character_maximum_length, col.is_nullable
 FROM information_schema.tables tab
 JOIN information_schema.columns col
 ON tab.table_name = col.table_name
 AND tab.table_type = 'BASE TABLE' AND tab.table_schema = '스키마 이름'  AND col.table_schema = '스키마 이름' ) c
 ON b.relname = c.table_name AND a.objsubid = c.ordinal_position
 LEFT OUTER JOIN
 ( SELECT u.table_name, u.column_name, u.constraint_name, u.ordinal_position
 FROM information_schema.key_column_usage AS u
 INNER JOIN information_schema.table_constraints AS t
 ON t.constraint_name = u.constraint_name
 WHERE u.table_schema = '스키마 이름' AND u.table_schema = t.table_schema AND t.constraint_type = 'PRIMARY KEY' ) d
 ON b.relname = d.table_name AND c.column_name = d.column_name
ORDER BY table_name, c.ordinal_position NULLS FIRST;