Postgresql DDL 테이블 정보 추출

by

1. 개요

  • 이번 포스트에서는 PostgreSQL에서 DDL 정보를 추출하기 위한 SQL을 포스팅하겠습니다.
  • 개발 시 테이블 정의 등을 작성해야 하는데 일일이 작성하기 번거롭다.
  • DDL SQL을 이용하여 테이블명/컬럼명/타입 정보를 출력해 보자.

2. 설치 및 문서화 정보

  • Windows의 경우 Postgresl 사이트에 접속하여 쉽게 설치할 수 있습니다.
  • Linux의 경우 아래 링크를 참조하십시오.
  • https://arckwon.entry/AWS-EC2-Ubuntu%EC%97%90-postgresql-%EC%84%A4%EC%B9%98%ED%95%98%EA%B8%B0

(AWS EC2) Ubuntu에 postgresql 설치

1. 개요 설치부터 pgAdmin 연결까지 우분투 버전에 따라 설치할 수 있는 postgresql 버전이 다릅니다. 2. 설치 환경 OS: AWS EC2 ubuntu20.04 DB: PostgreSQL14 https://www.postgresql.org/download/linux/ubuntu/ 참고자료 보기

'추천 관련글,

arckwon.tistory.com

  • 문서 문서는 현재 최대 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. 테이블 설명 SQL

  • 마지막 where 절에 테이블 이름 입력
  • 일반적으로 개발 출력을 작성할 때 적절한 SQL을 사용하여 Excel에서 쉽게 작성할 수 있습니다.
   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을 실행하면 다음과 같은 결과가 반환됩니다.