PostgreSQL DB에있는 모든 컬럼 조회 및 테이블별 정렬

 

SELECT TABLE_NAME as 테이블이름, column_name as 컬럼명,  
is_nullable as null가능여부,  
data_type as 데이터타입,  
character_maximum_length as 최대길이,  
character_octet_length,  
numeric_precision,  
numeric_precision_radix,  
numeric_scale  
  FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_CATALOG = 'DB이름'
 and table_schema = 'public'(혹은 스키마 이름) 
 ORDER BY 테이블이름, ORDINAL_POSITION;

 

주황색칠해진 부분만 조회하고싶은 db이름, 스키마이름을 적어주면된다

스키마는 따로 변경한 내용이 없다면 public을 그대로 두면된다

 

 

++ 테이블 조회와 관련된  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; 
 
테이블 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