출처:https://blog.nachal.com/m/1631
1. 테이블의 주석(코멘트/설명) 출력
/* 테이블 주석 셀렉트 */
SELECT N.NSPNAME, C.RELNAME, OBJ_DESCRIPTION(C.OID)
FROM PG_CATALOG.PG_CLASS C INNER JOIN PG_CATALOG.PG_NAMESPACE N ON C.RELNAMESPACE=N.OID
WHERE C.RELKIND = 'r'
AND NSPNAME = '데이터베이스명'
AND RELNAME = '테이블명'
2. 테이블의 컬럼명과 주석(코멘트/설명) 출력
/* 컬럼 주석 셀렉트 */
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 PD.OBJSUBID<>0
AND PS.RELID=PD.OBJOID
AND PD.OBJOID=PA.ATTRELID
AND PD.OBJSUBID=PA.ATTNUM
AND PS.SCHEMANAME='스키마'
AND PS.RELNAME='테이블'
ORDER BY PS.RELNAME, PD.OBJSUBID
3. 주석과 컬럼 타입
test SELECT PS.SCHEMANAME AS SCHEMA
, PS.RELNAME
, PA.ATTNAME
, CASE WHEN POSITION('(' IN PD.DESCRIPTION) > 1 THEN SUBSTRING(PD.DESCRIPTION, 1, POSITION('(' IN PD.DESCRIPTION)-1)
ELSE PD.DESCRIPTION END AS ATTKRNAME -- 한글컬럼명 괄호 전까지만 자르기
, CASE WHEN PA.ATTNUM = ANY(PC.CONKEY) THEN 'PK' ELSE '' END AS PK
, TBL.DATA_TYPE
FROM PG_STAT_USER_TABLES PS
, INFORMATION_SCHEMA.COLUMNS TBL
, PG_DESCRIPTION PD
, PG_ATTRIBUTE PA
, PG_CATALOG.PG_CONSTRAINT PC
WHERE PS.SCHEMANAME IN ('sppdb', 'spsdb')
AND TBL.TABLE_SCHEMA = PS.SCHEMANAME
AND TBL.TABLE_NAME = RELNAME
AND PS.RELID = PD.OBJOID
AND PD.OBJSUBID != 0
AND PD.OBJOID = PA.ATTRELID
AND PD.OBJSUBID = PA.ATTNUM
AND PS.RELID = PC.CONRELID
AND PS.RELNAME = 'tb_pymt_ctgr';
