[PostgreSQL] dictionary 쿼리

이 포스팅에서는 직접 사용했던 부분에 대해서만 간략히 기재하려고 한다.

PostgreSQL documentation에서 INFORMATION_SCHEMA의 테이블에 대한 상세한 내용을 확인할 수 있다.

 

 

https://www.postgresql.org/docs/current/information-schema.html

Chapter 37. The Information Schema

 

 

테이블 정보 :

INFORMATION_SCHEMA.TABLES

table_catalog	테이블이 속한 데이터베이스 이름 (항상 최근 데이터베이스)
table_schema	테이블이 속한 스키마 이름
table_name	테이블 이름
table_type	테이블 유형 (ex. BASE TABLE, VIEW, FOREIGN, TEMPORARY)

PG_TABLES (VIEW)

schemaname	테이블이 속한 스키마 이름
tablename	테이블 이름
tableowner	테이블 소유자

 

테이블 설명 정보 : PG_CATALOG

SELECT T.TABLE_NAME,
               (SELECT TABLEOWNER
                FROM     PG_TABLES
                WHERE  TABLENAME = T.TABLE_NAME) USER_NAME,
                PG_CATALOG.OBJ_DESCRIPTION(PGC.OID, 'pg_class') AS COMMENTS
FROM     INFORMATION_SCHEMA.TABLES T
                INNER JOIN PG_CATALOG.PG_CLASS PGC
                                      ON T.TABLE_NAME = PGC.RELNAME
WHERE   T.TABLE_TYPE = 'BASE TABLE'
                 AND T.TABLE_SCHEMA = 'public'

-테이블의 소유자, 설명을 조회하는 쿼리문

pg_catalog 의 obj_description 함수를 사용하여 설명을 조회할 수 있다.

 

 

컬럼 정보 : INFORMATION_SCHEMA.COLUMNS

table_catalog	테이블이 속한 데이터베이스 이름 (항상 최근 데이터베이스)
table_schema	테이블이 속한 스키마 이름
table_name	테이블 이름
column_name	열 이름
ordinal_position	열의 순서 위치 (1부터 시작)
is_nullable	NULL 허용여부
data_type	열의 데이터 유형 (built-in type, ARRAY, USER-DEFINED)
character_maximum_length	문자열의 경우 최대 문자 길이
udt_name	열 데이터 유형의 이름

 

SELECT  C.RELNAME AS TABLE_NAME,
                A.ATTNAME AS COLUMN_NAME,
               (SELECT COL_DESCRIPTION(A.ATTRELID, A.ATTNUM)) AS COMMENTS
FROM     PG_CATALOG.PG_CLASS C
INNER JOIN PG_CATALOG.PG_ATTRIBUTE A
                     ON A.ATTRELID = C.OID
WHERE   C.RELNAME IN (SELECT T.TABLE_NAME
                                             FROM    INFORMATION_SCHEMA.TABLES T
                                             INNER JOIN PG_CATALOG.PG_CLASS PGC
                                                                     ON T.TABLE_NAME = PGC.RELNAME
                                             WHERE T.TABLE_TYPE = 'BASE TABLE'
                                                            AND T.TABLE_SCHEMA = 'public')
AND A.ATTNUM > 0 AND A.ATTISDROPPED IS FALSE
AND PG_CATALOG.PG_TABLE_IS_VISIBLE(C.OID)

-컬럼의 설명을 조회하는 쿼리문

pg.catalog의 col_description 함수를 사용하여 설명을 조회할 수 있다.

 

제약조건 정보 :

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

SELECT CC.COLUMN_NAME AS COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC      ,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC
WHERE TC.TABLE_CATALOG = COLS.TABLE_CATALOG
              AND TC.TABLE_NAME = #{tableName}
              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

-테이블의 PK 제약조건에 지정된 컬럼 조회