Postgresql 컬럼 및 코멘트 조회

컬럼 조회

select
    table_name,
    column_name,
    udt_name as "type",
    character_maximum_length as length
    ,
    (case
        when is_nullable = 'NO' then 'N'
        else ''
    end) as "nullable"
from
    INFORMATION_SCHEMA.COLUMNS
where
    table_name = '테이블명'
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