Postgresql ddl 테이블정보 추출하기

1.  개요

• 이번포스팅에서는 Postgresql에서 ddl정보를 추출하는 SQL를 포스팅한다.
• 개발하면서 테이블정의서 등을 작성해야 되는데 일일이 작성하는것은 번거롭다.
• DDL SQL을 활용하여 테이블명/컬럼명/타입 정보등을 출력해보자.

 

2. 설치및 문서 정보

• 윈도우의 경우 Postgresl 사이트 에 접속하면 쉽게 설치할수있다.
• 리눅스의 경우 아래 링크를 참고하자.
• https://arckwon.tistory.com/entry/AWS-EC2-Ubuntu%EC%97%90-postgresql-%EC%84%A4%EC%B9%98%ED%95%98%EA%B8%B0

[AWS EC2] Ubuntu에 postgresql 설치하기

 

• 현재 Document 문서는 15버전까지 존재한다.

 

3. 테이블정보 SQL

• 해당스키마에 등록된 테이블 정보를 조회하는 SQL이다.
• 실행시 테이블이 조회된다.

SELECT tablename
  FROM PG_TABLES
 WHERE TABLENAME LIKE 'users%'
 ORDER BY TABLENAME ASC

 

 

4. 스키마 / 테이블명 / 테이블COMMENT SQL

• 시스템테이블 PG_CATALOG 카테고리의 PG_CLASS테이블과 PG_NAMESPACE 테이블정보로 확인한다.

SELECT N.NSPNAME, C.RELNAME, OBJ_DESCRIPTION(C.OID)
  FROM PG_CATALOG.PG_CLASS C INNER JOIN PG_CATALOG.PG_NAMESPACE N ON C.RELNAMESPACE=N.OID
 WHERE C.RELKIND = 'r'
   AND N.NSPNAME = 'public'
 ORDER BY  C.RELNAME ASC

 

 

5. 테이블명 / 컬럼명 / 컬럼주석 SQL

• 시스템 테이블의 PG_STAT_ALL_TABLES , PG_DESCRIPTION, PG_ATTRIBUTE 정보로 확인한다.

SELECT PS.RELNAME AS TABLE_NAME,
       PA.ATTNAME AS COLUMN_NAME,
       PD.DESCRIPTION AS COLUMN_COMMENT
  FROM PG_STAT_ALL_TABLES PS, PG_DESCRIPTION PD, PG_ATTRIBUTE PA
 WHERE PD.OBJSUBID<>0
   AND PS.RELID=PD.OBJOID
   AND PD.OBJOID=PA.ATTRELID
   AND PD.OBJSUBID=PA.ATTNUM
 ORDER BY PS.RELNAME, PD.OBJSUBID

 

 

6. 테이블 DESCRIPTION SQL

• 마지막 where 조건에 테이블명 입력
• 보통 개발 산출물 작성할때 해당 SQL을 활용하면 엑셀로 쉽게 작성할수 있다.

   SELECT
    COLS.TABLE_NAME,
    COLS.COLUMN_NAME,
    C.COMMENT,
    UPPER(COLS.UDT_NAME) AS "TYPE",


(CASE
    WHEN COLS.CHARACTER_MAXIMUM_LENGTH IS NULL THEN ''
    ELSE CAST(COLS.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)
END) AS LENGTH,
(CASE
    WHEN COLS.IS_NULLABLE = 'NO' THEN 'Y'
    ELSE 'N'
END) AS "NULLABLE",
(CASE
    WHEN COLS.IS_IDENTITY = 'NO' THEN ''
    ELSE 'PK'
END) AS IS_IDENTITY ,
COLS.COLUMN_DEFAULT AS DEFAULT,
 
(SELECT OBJ_DESCRIPTION(C.OID)  FROM PG_CATALOG.PG_CLASS C
   WHERE C.RELNAME = COLS.TABLE_NAME)  AS TABLE_DESC
FROM
    INFORMATION_SCHEMA.COLUMNS COLS
INNER JOIN (
    SELECT
        C.RELNAME AS TABLE_NAME,
        A.ATTNAME AS "COLUMN_NAME",
        (
        SELECT
            COL_DESCRIPTION(A.ATTRELID, A.ATTNUM)) AS COMMENT
    FROM
            PG_CATALOG.PG_CLASS C
    INNER JOIN PG_CATALOG.PG_ATTRIBUTE A ON
        A.ATTRELID = C.OID
    WHERE
            C.RELNAME IN (
        SELECT
            T.TABLE_NAME
        FROM
            INFORMATION_SCHEMA.TABLES T
        INNER JOIN PG_CATALOG.PG_CLASS PGC ON
            T.TABLE_NAME = PGC.RELNAME
        WHERE
            T.TABLE_TYPE = 'BASE TABLE'
            AND T.TABLE_SCHEMA = 'PUBLIC'
        ORDER BY
            TABLE_NAME)
        AND A.ATTNUM > 0
        AND A.ATTISDROPPED IS FALSE
        AND PG_CATALOG.PG_TABLE_IS_VISIBLE(C.OID)
    ORDER BY
        RELNAME,
        A.ATTRELID,
        A.ATTNUM) C ON
    (COLS.TABLE_NAME = C.TABLE_NAME
        AND COLS.COLUMN_NAME = C.COLUMN_NAME)
WHERE
    COLS.TABLE_NAME = '테이블명';

 

• 위 SQL실행시 아래와 같은 결과가 출력된다.