PostgreSQL: INFORMATION_SCHEMA 표준 SQL 활용 예

1. INFORMATION_SCHEMA

INFORMATION_SCHEMA는 SQL 표준에서 정의된 메타데이터 조회용 시스템 스키마이다.

주요 특징

• 데이터베이스, 테이블, 컬럼, 제약조건 등 구조 정보 제공
• 읽기 전용(View 기반) 스키마
• 쿼리 실행 시점에 실시간으로 메타데이터 생성
• 사용자 권한에 따라 조회 가능한 정보 제한
• DBMS 간 이식성(Portable SQL) 확보 가능

PostgreSQL에서는 information_schema와 더불어 pg_catalog가 실무 메타데이터의 핵심을 이룬다.

2. PostgreSQL에서 INFORMATION_SCHEMA의 위치

사용 목적

• DBMS 독립적인 스크립트
• 멀티 DB 지원 애플리케이션
• CI/CD 구조 검증
• 표준 SQL 기반 문서화

사용 비권장 영역

• 성능 민감한 메타데이터 조회
• 인덱스, 파티션, 시퀀스 상세 분석
• 내부 통계 정보

원칙

• 표준성 필요 → information_schema
• 운영/성능/내부 구조 → pg_catalog

3. 스키마 / 데이터베이스 조회

전체 스키마 목록

SELECT schema_name
FROM information_schema.schemata;

특정 패턴 스키마 조회

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE 'test%';

PostgreSQL 네이티브 방식

SELECT nspname
FROM pg_namespace;

4. 테이블 목록 및 타입 조회

특정 스키마의 테이블 목록

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

테이블 / 뷰 구분

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public';

PostgreSQL 내부 카탈로그

SELECT relname, relkind
FROM pg_class
JOIN pg_namespace n ON n.oid = relnamespace
WHERE nspname = 'public';

5. 컬럼(필드) 상세 정보 조회

표준 SQL 방식

SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'user';

PostgreSQL 내부 방식

SELECT attname, atttypid, attnotnull
FROM pg_attribute
WHERE attrelid = 'user'::regclass;

활용

• ORM 매핑 자동 생성
• 스키마 변경(diff) 검증
• Nullable 정책 검증

6. 제약조건(PK / FK / UK)

제약조건 컬럼 매핑 (표준)

SELECT *
FROM information_schema.key_column_usage
WHERE table_schema = 'public'
  AND table_name = 'user';

Primary Key만 조회

SELECT column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'public'
  AND table_name = 'user'
  AND constraint_name = 'user_pkey';

PostgreSQL 실전 방식

SELECT *
FROM pg_constraint;

항목	INFORMATION_SCHEMA	pg_catalog
PK / FK	가능	가능
CHECK 제약	제한적	완전
DEFERRABLE	불가	가능
성능	보통	우수

7. Foreign Key 관계 분석

SELECT 
    table_name,
    column_name,
    referenced_table_name,
    referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'public'
  AND referenced_table_name IS NOT NULL;

활용

• ERD 자동 생성
• 삭제/업데이트 영향도 분석
• 데이터 정합성 점검

8. 인덱스 정보 조회

PostgreSQL 권장 방식

SELECT *
FROM pg_indexes
WHERE schemaname = 'public';

주의

• PostgreSQL에서 information_schema는 인덱스 정보가 제한적임
• 실무에서는 반드시 pg_catalog 사용

9. 테이블 용량 / 스토리지 분석

PostgreSQL은 INFORMATION_SCHEMA에 용량 정보가 없음

SELECT
    relname AS table_name,
    pg_total_relation_size(relid) / 1024 / 1024 AS total_mb
FROM pg_catalog.pg_statio_user_tables;

10. 권한 기반 객체 조회

SELECT table_schema, table_name
FROM information_schema.tables;

• 현재 사용자 권한 기준으로 자동 필터링

• 보안 점검 및 계정 분리 검증에 활용

11. 컬럼 영향도 분석

SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_name = 'user_id';

활용

• 컬럼명 변경 영향 분석
• 공통 컬럼 표준화

12. 객체 존재 여부 확인

SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name = 'user';

활용

• 배포 스크립트 안전 처리
• 자동 설치 / 마이그레이션

13. PostgreSQL vs Oracle 메타데이터 철학 비교

항목	PostgreSQL	Oracle
표준 SQL 준수	높음	낮음
INFORMATION_SCHEMA	정식 지원	참고 수준
메타데이터 핵심	information_schema + pg_catalog	DBA / ALL / USER_
권한 처리	단일 카탈로그 + 필터링	View 분리
실무 접근 방식	표준 → 내부 확장	자체 표준 중심

14. PostgreSQL 실무 권장 패턴

표준/이식성

information_schema.*

운영/성능

pg_catalog.*

혼합 패턴 (가장 일반적)

information_schema.tables
pg_indexes
pg_constraint