[MyBatis] PostgreSQL 에서 테이블의 컬럼 정보 조회하기

<select id="selectTableColumnList" parameterType="map"
        resultType="me.dailycode.dto.TestDTO">
    SELECT
           C.TABLE_NAME       AS TABLE_NM,
           C.COLUMN_NAME      AS COLUMN_NM,
           C.DATA_TYPE        AS DATA_TY,
           PGD.DESCRIPTION    AS COLUMN_NCM,
           C.ORDINAL_POSITION AS COLUMN_ORDR
    FROM PG_CATALOG.PG_STATIO_ALL_TABLES AS ST
    INNER JOIN PG_CATALOG.PG_DESCRIPTION PGD ON (PGD.OBJOID = ST.RELID)
    INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON (
        PGD.OBJSUBID = C.ORDINAL_POSITION AND
        C.TABLE_SCHEMA = ST.SCHEMANAME AND
        C.TABLE_NAME = ST.RELNAME
    ) AND (
        LOWER(C.TABLE_CATALOG) = #{database} AND
        LOWER(C.TABLE_SCHEMA) = #{schema} AND
        LOWER(C.TABLE_NAME) = #{table}
    )
    ORDER BY COLUMN_ORDR
</select>

• LOWER(C.TABLE_CATALOG) = #{database} AND 는 필요하면 작성, 아니면 지우기
• 주의! 테이블에 comment 를 생성하지 않았다면 PG_CATALOG.PG_DESCRIPTION 가 조회되지 않는다!