information_schema를 활용한 PostgreSQL 데이터베이스 객체 관리 및 조회

PostgreSQL은 오픈소스 관계형 데이터베이스 관리 시스템(RDBMS)으로, 데이터베이스 내에서 발생하는 다양한 작업을 효율적으로 처리하는 강력한 기능을 제공합니다. 그 중 하나가 바로 information_schema입니다. information_schema는 PostgreSQL을 포함한 대부분의 관계형 데이터베이스에서 제공하는 표준 스키마로, 데이터베이스 객체에 대한 중요한 메타데이터를 조회하는 데 사용됩니다. 이 글에서는 information_schema가 무엇인지, 그리고 이를 활용하여 데이터베이스의 테이블, 컬럼, 제약 조건 등 다양한 정보를 어떻게 조회할 수 있는지 설명합니다.

 

 

---

information_schema란 무엇인가?

 

information_schema는 데이터베이스의 메타데이터를 표준화된 방법으로 제공하는 스키마입니다. 메타데이터는 데이터베이스 내의 객체들(예: 테이블, 컬럼, 뷰, 제약 조건 등)에 대한 정보를 담고 있는 데이터입니다. PostgreSQL에서는 information_schema를 통해 데이터베이스의 구조를 파악하고, 이를 활용하여 관리 및 자동화 작업을 할 수 있습니다.

PostgreSQL에서 제공하는 information_schema는 ANSI SQL 표준을 준수하기 때문에, 다른 데이터베이스 시스템에서도 동일한 방식으로 사용 가능합니다. 이를 통해 데이터베이스를 다른 시스템으로 이식할 때나 다양한 DBMS 간에 일관된 방식으로 메타데이터를 조회할 수 있다는 큰 장점이 있습니다.

 

 

 

---

information_schema에서 제공하는 주요 테이블

information_schema는 여러 가지 테이블(또는 뷰)로 구성되어 있으며, 주요 테이블은 다음과 같습니다.

 

1. information_schema.tables

이 테이블은 데이터베이스 내의 모든 테이블에 대한 정보를 제공합니다. 이를 통해 각 테이블의 이름, 소속 스키마, 테이블 종류(Base Table 또는 View)를 알 수 있습니다.

• table_catalog: 테이블이 속한 데이터베이스의 이름
• table_schema: 테이블이 속한 스키마의 이름
• table_name: 테이블의 이름
• table_type: 테이블의 종류 (예: BASE TABLE, VIEW)

예시: 특정 스키마 내 모든 테이블 조회

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

 

2. information_schema.columns

이 테이블은 데이터베이스 내 모든 테이블의 컬럼에 대한 정보를 제공합니다. 컬럼의 이름, 데이터 타입, 길이, NULL 허용 여부 등 다양한 정보를 조회할 수 있습니다.

• table_schema: 컬럼이 속한 테이블의 스키마
• table_name: 컬럼이 속한 테이블의 이름
• column_name: 컬럼의 이름
• data_type: 컬럼의 데이터 타입
• is_nullable: 컬럼이 NULL을 허용하는지 여부

예시: 특정 테이블의 컬럼 정보 조회

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

 

 

반응형

 

 

3. information_schema.key_column_usage

이 테이블은 데이터베이스 내의 키 정보(프라이머리 키, 외래 키 등)를 제공합니다. 테이블의 각 컬럼이 어떤 제약 조건에 의해 사용되는지 확인할 수 있습니다.

• constraint_name: 제약 조건의 이름
• table_schema: 제약 조건이 적용된 테이블의 스키마
• table_name: 제약 조건이 적용된 테이블의 이름
• column_name: 제약 조건이 적용된 컬럼의 이름

예시: 특정 테이블의 키 정보 조회

SELECT constraint_name, column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'public' AND table_name = 'orders';

 

4. information_schema.views

이 테이블은 데이터베이스 내의 모든 뷰(VIEW)에 대한 정보를 제공합니다. 각 뷰의 이름과 뷰를 정의하는 SQL 문을 조회할 수 있습니다.

• table_schema: 뷰가 속한 스키마
• table_name: 뷰의 이름
• view_definition: 뷰를 정의하는 SQL 문

예시: 특정 스키마의 모든 뷰 정보 조회

SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'public';

 

5. information_schema.constraint_column_usage

이 테이블은 특정 제약 조건이 적용된 컬럼에 대한 정보를 제공합니다. 데이터베이스 내에서 어떤 컬럼이 특정 제약 조건에 의해 사용되고 있는지 확인할 수 있습니다.

• constraint_name: 제약 조건의 이름
• table_schema: 제약 조건이 적용된 테이블의 스키마
• table_name: 제약 조건이 적용된 테이블의 이름
• column_name: 제약 조건이 적용된 컬럼의 이름

예시: 특정 제약 조건이 적용된 컬럼 조회

SELECT constraint_name, table_name, column_name
FROM information_schema.constraint_column_usage
WHERE constraint_name = 'your_constraint_name';

 

 

 

---

information_schema 활용 예시

 

1. 전체 테이블 목록 조회

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

 

2. 테이블의 컬럼 및 데이터 타입 조회

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

 

3. 테이블의 제약 조건 및 키 정보 조회

SELECT constraint_name, column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'public' AND table_name = 'orders';

 

 

---

information_schema의 장점

 

1. 표준화된 접근: information_schema는 ANSI SQL 표준을 준수하므로 PostgreSQL뿐만 아니라 다른 관계형 데이터베이스 시스템에서도 동일한 방식으로 메타데이터를 조회할 수 있습니다.
2. 다양한 데이터베이스 객체 조회: 이 뷰는 테이블, 컬럼, 제약 조건, 뷰 등 다양한 객체의 정보를 제공하므로 데이터베이스의 구조를 파악하는 데 유용합니다.
3. 보안: information_schema는 사용자가 접근할 수 있는 최소한의 메타데이터만 노출하므로 보안에 안전하게 데이터베이스의 구조를 확인할 수 있습니다.

 

 

 

---

 

 

 

information_schema는 PostgreSQL에서 제공하는 강력한 메타데이터 조회 도구입니다. 이를 통해 데이터베이스의 테이블, 컬럼, 제약 조건 등 다양한 객체의 정보를 표준화된 방식으로 쉽게 조회할 수 있습니다. 또한, 데이터베이스 구조를 파악하고, 이를 바탕으로 관리 및 자동화 작업을 수행할 수 있어 데이터베이스의 효율적인 운영과 유지 보수에 큰 도움이 됩니다. information_schema는 ANSI SQL 표준을 준수하므로 다른 관계형 데이터베이스 시스템에서도 동일하게 활용할 수 있는 이식성 있는 도구입니다.