PostgreSQL에서 테이블과 뷰만 조회하는 쿼리 작성법: 오라클과의 차이점

프로그램을 개발할 때 데이터베이스에 대한 메타정보를 조회하는 쿼리를 작성하는 일이 많습니다. 특히 테이블의 이름, 설명, 또는 주석을 가져오는 경우가 흔합니다. 오라클에서는 ALL_TAB_COMMENTS와 같은 시스템 테이블을 통해 테이블에 대한 설명을 쉽게 조회할 수 있지만, PostgreSQL에서는 그 방식이 조금 다릅니다. PostgreSQL은 메타데이터를 pg_class, pg_description 등의 시스템 테이블을 통해 제공합니다.

이번 글에서는 PostgreSQL에서 테이블과 뷰에 대한 메타정보를 조회하는 방법을 소개합니다. 오라클에서 작성한 쿼리를 PostgreSQL에 맞게 변환하는 과정과 그에 맞는 최적화 방법을 다룹니다. 

 

 

 

 

---

1. 오라클 쿼리에서 PostgreSQL 쿼리로의 변환

 

오라클 쿼리 예시

오라클에서는 ALL_TAB_COMMENTS를 사용하여 주석을 조회할 수 있습니다. 예를 들어, 다음과 같은 쿼리를 사용하여 특정 패턴에 맞는 테이블에 대한 정보를 조회할 수 있습니다.

SELECT TABLE_NAME    CODE, 
       MAX(COMMENTS) TEXT  
  FROM ALL_TAB_COMMENTS    
 WHERE (TABLE_NAME LIKE 'TT%' OR TABLE_NAME LIKE 'V_TT%') 
   AND LENGTH(TABLE_NAME) IN (8, 10) 
 GROUP BY TABLE_NAME 
 ORDER BY TABLE_NAME;

위 쿼리는 테이블 이름이 TT% 또는 V_TT%로 시작하며, 길이가 8 또는 10인 테이블들에 대해 주석을 조회하는 쿼리입니다.

 

PostgreSQL 쿼리로 변환

PostgreSQL에서는 pg_class, pg_description, pg_namespace 테이블을 사용하여 메타데이터를 조회합니다. 오라클의 ALL_TAB_COMMENTS에 해당하는 부분을 PostgreSQL에서는 pg_description과 pg_class를 조인하여 얻을 수 있습니다.

변환된 쿼리는 다음과 같습니다:

SELECT c.relname AS table_name, 
       MAX(d.description) AS text
  FROM pg_class c
  LEFT JOIN pg_description d ON c.oid = d.objoid
  LEFT JOIN pg_namespace n ON c.relnamespace = n.oid
 WHERE (c.relname LIKE 'TT%' OR c.relname LIKE 'V_TT%')
   AND LENGTH(c.relname) IN (8, 10)
   AND n.nspname = 'public'  -- 필터링을 원하는 스키마로 수정
   AND (c.relkind = 'r' OR c.relkind = 'v')  -- 일반 테이블(r)과 뷰(v)를 포함
 GROUP BY c.relname
 ORDER BY c.relname;

 

반응형

 

 

 

• pg_class.relkind 컬럼은 객체의 종류를 나타내며, 아래와 같은 값들을 가질 수 있습니다:
  • 'r': 일반 테이블
  • 'i': 인덱스
  • 'v': 뷰
  • 'S': 시퀀스
  • 'm': 매터리얼라이즈드 뷰
  • 'c': 컴포지트 타입

따라서 인덱스 테이블을 제외하고 실제 일반 테이블만 조회하려면, relkind가 'r'인 항목만 필터링하면 됩니다.

 

 

---

 

2. PostgreSQL에서 테이블과 뷰 조회하기

 

PostgreSQL에서 메타데이터를 조회할 때는 pg_class, pg_description, pg_namespace 테이블을 조인하여 원하는 정보를 가져옵니다. 주요 요소는 다음과 같습니다:

 

• pg_class: 테이블, 뷰, 인덱스 등 데이터베이스 객체에 대한 정보를 저장하는 시스템 테이블입니다. relname 컬럼은 객체의 이름을 나타냅니다.
• pg_description: 객체에 대한 주석을 저장하는 테이블입니다. description 컬럼은 해당 객체에 대한 주석을 포함합니다.
• pg_namespace: 테이블이 속한 스키마에 대한 정보를 제공하는 테이블입니다. nspname 컬럼은 스키마의 이름을 나타냅니다.
• relkind: pg_class의 컬럼 중 하나로 객체의 종류를 나타냅니다. 'r'은 일반 테이블, 'v'는 뷰를 의미합니다.

 

조건 필터링

• c.relkind = 'r' OR c.relkind = 'v': relkind가 'r'이면 일반 테이블, 'v'이면 뷰입니다. 이 조건을 사용하여 인덱스나 시퀀스와 같은 다른 객체를 제외하고 테이블과 뷰만 조회합니다.

 

 

 

---

3. PostgreSQL에서 인덱스 제외하기

 

위 쿼리에서는 pg_class에서 조회할 때 인덱스를 제외하고 일반 테이블과 뷰만 포함하도록 relkind 조건을 추가했습니다. pg_class에서 조회할 경우, 인덱스가 포함될 수 있기 때문에 relkind = 'r' (일반 테이블)과 relkind = 'v' (뷰)만을 선택하는 조건을 추가하는 것이 중요합니다.

 

AND (c.relkind = 'r' OR c.relkind = 'v')  -- 일반 테이블(r)과 뷰(v)를 포함

이 조건을 통해 불필요한 인덱스 객체가 결과에 포함되지 않도록 할 수 있습니다.

 

 

 

 

---

4. pg_description과 pg_class의 관계

 

PostgreSQL에서 객체에 대한 주석을 조회하려면 pg_description 테이블과 pg_class 테이블을 조인해야 합니다. pg_class 테이블에는 테이블, 뷰, 시퀀스 등의 객체 정보가 저장되어 있으며, pg_description은 각 객체에 대한 주석을 담고 있습니다.

 

조인 구조:

• pg_class와 pg_description은 객체 ID (oid)를 기준으로 조인됩니다.
• pg_class.relnamespace는 해당 객체가 속한 스키마를 나타내므로, 필요한 경우 pg_namespace와도 조인하여 특정 스키마에 속한 테이블만 필터링할 수 있습니다.

 

 

 

---

5. PostgreSQL 쿼리 최적화

 

PostgreSQL에서는 메타데이터를 조회할 때 pg_class, pg_description, pg_namespace 등의 시스템 테이블을 사용합니다. 성능을 최적화하기 위해 필요한 조건을 필터링하여 불필요한 데이터를 조회하지 않도록 해야 합니다. 예를 들어, relkind 조건을 통해 테이블과 뷰만 필터링하고, nspname을 통해 특정 스키마에 속하는 테이블만 조회하도록 할 수 있습니다.

 

 

---

6. 결론

 

PostgreSQL에서 오라클 쿼리를 변환하여 테이블과 뷰만 조회하는 쿼리를 작성하는 방법에 대해 알아보았습니다. PostgreSQL은 메타데이터 정보를 pg_class, pg_description, pg_namespace 등의 시스템 테이블을 통해 제공하므로 이를 잘 활용해야 합니다. 또한, 불필요한 인덱스를 제외하고 필요한 테이블과 뷰만 조회하기 위해 relkind 조건을 사용하는 것이 중요합니다.

이 글은 구글 SEO 기준에 맞춰 관련된 키워드를 포함하고, 더 많은 사람들에게 유용한 정보를 제공할 수 있도록 최적화되었습니다. PostgreSQL에서의 메타데이터 조회에 대한 이해를 돕고, 효율적인 쿼리 작성 방법을 소개했습니다.