PostgreSQL Information_Schema 뷰 이해하기

이 글에서는 PostgreSQL에 내장된 시스템 중 하나인 information_schema를 자세히 살펴볼게요.

계속 읽기 전에, 저희가 만든 현대적이고 빠른 PostgreSQL 데이터베이스 관리 도구인 Beekeeper Studio를 소개해 드리고 싶어요. 테이블과 컬럼부터 인덱스, 외래 키까지 같은 메타데이터를 사이드바와 구조 뷰에서 보여 주고, PostgreSQL에서는 무료로 사용할 수 있어요. 여기서 다운로드하세요.

Information_Schema란?

Information_schema는 PostgreSQL 데이터베이스 객체에 대한 정보를 담은 표준 뷰 집합으로 이루어진 시스템 카탈로그예요. 쉽게 말해 현재 데이터베이스에 대한 정보를 담고 있는 메타 데이터베이스죠. 표준을 준수하는 모든 SQL 데이터베이스 엔진에 존재하기 때문에 마이그레이션할 때 특히 유용해요.

information_schema는 읽기 전용이라 새 정보를 삽입하거나 기존 데이터를 수정할 수 없다는 점에 유의하세요. 이제 다양한 작업에 유용하게 쓸 수 있는 information_schema 안의 여러 뷰를 살펴볼게요.

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

이 코드는 모든 PostgreSQL 버전과 호환돼요.
실행하면 information_schema에서 사용할 수 있는 모든 뷰 목록이 출력돼요.

Information_Schema를 사용하는 이유

information_schema는 데이터베이스 구조를 더 효과적으로 다루려 할 때 매우 중요해요. 특히 다음과 관련된 작업에서요:

• 테이블
• 컬럼
• 인덱스
• 트리거
• 제약 조건
• 스키마

Information_Schema.Tables

information_schema.tables 뷰에는 연결된 데이터베이스의 모든 테이블에 대한 정보가 담겨 있어요.

SELECT table_name 
FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' 
AND table_schema NOT IN ('pg_catalog', 'information_schema');

이 코드는 모든 PostgreSQL 버전과 호환돼요. 실행하면 데이터베이스에 있는 모든 기본 테이블 목록이 출력돼요.

Information_Schema.Columns

information_schema.columns 뷰에서는 데이터베이스 컬럼의 세부 정보를 조회할 수 있어요.

SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name   = 'your_table';

이 코드는 모든 PostgreSQL 버전과 호환돼요. ‘your_table’을 실제 테이블 이름으로 바꾸면 해당 테이블의 모든 컬럼과 데이터 타입 목록을 얻을 수 있어요.

Information_Schema.Triggers

트리거 정보는 디버깅하거나 의존 관계를 파악할 때 도움이 돼요. information_schema.triggers 뷰를 사용하면 데이터베이스에 연결된 트리거의 세부 정보를 조회할 수 있어요.

SELECT event_object_table, trigger_name, action_statement, action_timing 
FROM information_schema.triggers;

모든 PostgreSQL 버전과 호환되는 이 코드는 테이블 이름, 트리거 이름, 트리거 동작과 실행 시점을 출력해요.

Information_Schema.Key_Column_Usage

information_schema.key_column_usage 뷰는 데이터베이스의 외래 키 관계를 쉽게 이해할 수 있는 정보를 제공해요.

SELECT constraint_name, table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 'your_table';

‘your_table’을 실제 테이블 이름으로 바꾸고 모든 PostgreSQL 버전과 호환되는 이 코드를 실행하면, 해당 테이블의 모든 키에 대한 constraint_name, table_name, column_name, ordinal_position을 얻을 수 있어요.

결론

이 튜토리얼에서는 PostgreSQL의 information_schema를 자세히 알아봤어요. information_schema의 뷰를 활용하면 데이터베이스의 구조와 요소를 더 잘 제어하고 더 깊이 있는 인사이트를 얻을 수 있어요. 다른 SQL 데이터베이스에서 PostgreSQL로 옮겨 오는 경우라면, information_schema 뷰는 SQL 표준의 일부이기 때문에 특히 유용할 거예요.

PostgreSQL과의 여정은 여기서 끝나지 않아요. 더 고급 주제와 팁, 요령을 다루며 PostgreSQL의 광대한 세계를 더 깊이 탐구하는 가이드가 곧 이어지니 기대해 주세요.