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

테이블 목록 조회

SELECT RELNAME AS TABLE_NAME
  FROM PG_STAT_USER_TABLES

 

컬럼 목록 조회

SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_CATALOG = '데이터베이스명'
   AND TABLE_NAME    = '테이블명'
 ORDER BY ORDINAL_POSITION;

 

기본키 조회

SELECT CC.COLUMN_NAME AS COLUMN_NAME
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS       TC
      ,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC
 WHERE TC.TABLE_CATALOG   = '데이터베이스명'
   AND TC.TABLE_NAME      = '테이블명'
   AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
   AND TC.TABLE_CATALOG   = CC.TABLE_CATALOG
   AND TC.TABLE_SCHEMA    = CC.TABLE_SCHEMA
   AND TC.TABLE_NAME      = CC.TABLE_NAME
   AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME

 

테이블 COMMENT 조회

SELECT PS.RELNAME    AS TABLE_NAME
      ,PD.DESCRIPTION AS TABLE_COMMENT
  FROM PG_STAT_USER_TABLES PS
      ,PG_DESCRIPTION      PD
 WHERE PS.RELNAME  = '테이블명'
   AND PS.RELID   = PD.OBJOID
   AND PD.OBJSUBID  = 0

 

컬럼 COMMENT 조회

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