[PostgreSQL] 컬럼 정보 조회 / 컬럼 코멘트 조회

https://develop-me-z.tistory.com/6

PostgreSQL | 테이블/컬럼 정보조회 SQL

 

컬럼 정보 조회

SELECT TABLE_NAME, column_name, character_maximum_length, data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = '스키마명'
  AND TABLE_NAME = '테이블명'
  and table_schema = '스키마명'
ORDER BY ORDINAL_POSITION
;

 

컬럼 코멘트 조회

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 PS.SCHEMANAME = (SELECT SCHEMANAME
                       FROM PG_STAT_USER_TABLES
                       WHERE RELNAME = '테이블명')
  AND PS.RELNAME = '테이블명'
  AND PS.RELID = PD.OBJOID
  AND PD.OBJSUBID <> 0
  AND PD.OBJOID = PA.ATTRELID
  AND PD.OBJSUBID = PA.ATTNUM
ORDER BY PS.RELNAME, PD.OBJSUBID