[Postgresql] 테이블 각 Owner, size, 데이터 수 구하는 쿼리문

 

create or replace function count_rows_of_table(table_schema text, table_name text)
    returns numeric
    language plpgsql
as
$$
declare
    count numeric;
begin
    execute format('select count(*) from %s.%s', table_schema, table_name)
        into count;
    return count;
end;
$$;
SELECT n.nspname as "Schema",
       c.relname as "Name",
       pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
       TO_CHAR( pg_catalog.pg_table_size(c.oid)/1024 , '999,999,999,999,999') as "Size_MB",
       TRIM(TRIM(n.nspname)||'.'||TRIM(c.relname))AS TB_NAME,
       count_rows_of_table(n.nspname, c.relname) as exact_row_count
FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
  AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
  AND n.nspname <> 'sys'
  AND n.nspname <> 'dbo'
  AND n.nspname !~ '^pg_toast'
ORDER BY  pg_catalog.pg_table_size(c.oid) desc

 

위의 쿼리문을 입력하면 간단히 [Schema, Name(테이블 명), Owner, Size_MB(MB기준의 사이즈), tb_name(데이블 이름), row_count(테이블의 데이터 수)]를 구할 수 있다.