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

- 테이블 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
;

 

 

- 테이블 columns 조회

select column_name, character_maximum_length, data_type, is_nullable
from information_schema.columns
where table_catalog = '스케마명'
and table_name = '테이블명'
order by ordinal_position
;

다 조회하려면 'select * ' 로 바꾸면 됨

 

 

- 테이블에서 PK 조회

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
;