[Postgresql] 전체 테이블 count 조회 쿼리

 DB sync 작업을 위해 서로 다른 DB의 테이블 개수를 확인하는 과정이 필요했다.

전체 테이블의 count 를 구하기 위해 처음에는 loop를 사용해서 조회했지만,

검색해보니 postgresql 에서 제공하는 쿼리를 통해 훨씬 간단하게 구할 수 있었다.

 

1) LOOP 방식

DO $$
DECLARE
   rec  record;
BEGIN
	DELETE FROM tb_read_tb_cnt;	
   FOR rec IN
      SELECT *
      FROM   pg_tables
      WHERE  tablename NOT LIKE 'pg\_%'
      AND tableowner = 'user'
      ORDER BY tablename
   LOOP
      EXECUTE 
      'INSERT INTO tb_read_tb_cnt SELECT '''|| rec.tablename ||''' , count(*) cnt FROM  '
        || quote_ident(rec.schemaname) || '.'
        || quote_ident(rec.tablename);
   END LOOP;
END $$;

 

record 타입의 rec 변수는 반복문을 위해 선언한다. select 절의 레코드가 rec 변수에 들어가게 되고 

rec.{column} 과 같이 사용하게된다.

LOOP 방식의 단점은 DELETE 문과 INSERT 문을 통해 확인할 수 있듯이 "tb_read_tb_cnt"라는 테이블을 생성해야한다.

(컬럼은 tablename varchar(256), count int4)

pg_tables에서 pg_로 시작하지 않고 소유자가 해당 유저인 테이블을 가져와서 한줄씩 rec 변수에 선언한다.

그리고 EXECUTE 구문을 통해 한줄씩 위에서 만들어놓은 tb_read_tb_cnt 테이블에 insert 시킨다.

tb_read_tb_cnt 테이블을 조회하게 되면 위와 같은 테이블이 만들어진 것을 확인할 수 있다.

 

2) pg_stat_user_tables 조회

위에서 반복문을 만들어서 사용하고 있었지만, 사용하다보니 다른 DB에서도 반복물을 이용하려면  테이블을 계속

만들어줘야하는 불편한점이 있었다. 그래서 열심히 검색해본 결과 훨씬 간단하게 구할 수 있었다.

SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

 

pg_stat_user_tables 에서 schemaname은 문자 그대로 스키마이름이고, relname은 테이블명, n_live_up은 해당 테이블의

rows 카운트를 가져온다.

 

이런식으로 스키마명에 따라서 테이블의 row 카운트를 가져올 수 있으니 훨씬 간편하고 깔끔하다.

 

 

 

 

참조

http://www.gisdeveloper.co.kr/?p=4621 

PostgreSQL의 PL/pgSQL 튜토리얼 – 6 : 반복문 – GIS Developer