[PostgreSQL] 테이블 데이터 타입별 용량산정 쿼리

 /* POSTGRESQL 테이블 데이터 타입별 항목크기 산정 */ 

with t1 as (
    SELECT A.TABLENAME ,
	       A.COLUMN_NAME ,
	       A.DATA_TYPE ,
	       A.DATA_LENGTH ,
	       A.DATA_SIZE,
	       CASE WHEN A.DATA_SIZE < 250 THEN 1
	            ELSE 0
	       END BELOW_250,
	       CASE WHEN A.DATA_SIZE >= 250 THEN 1
	            ELSE 0
	       END OVER_250
      FROM (
           select TABLENAME , 
				   COLUMN_NAME,
				   DATA_TYPE,
				   DATA_LENGTH, 
				   ORDINAL_POSITION,
				   CASE WHEN DATA_TYPE = 'varchar' THEN CEIL(DATA_LENGTH * 0.7)
			            WHEN DATA_TYPE = 'numeric' THEN CEIL(DATA_LENGTH/2) + 1
			       ELSE DATA_LENGTH
			       END DATA_SIZE
             from 
             (
           select  TBL.TABLENAME , 
				   COL.COLUMN_NAME,
				   COL.udt_name                  AS DATA_TYPE,
				   COALESCE(COL.CHARACTER_MAXIMUM_LENGTH,COL.NUMERIC_PRECISION,COL.DATETIME_PRECISION,10) AS DATA_LENGTH, 
				   COL.ORDINAL_POSITION   
			  from PG_CATALOG.PG_TABLES TBL 
		      inner join INFORMATION_SCHEMA.COLUMNS COL on 
			        TBL.TABLENAME = COL.TABLE_NAME 
			  where COL.TABLE_SCHEMA = 'naqsifh'
			  -- and  tbl.tablename = 'tn_user_instt_entrps_r'
			 ) t
		) A
	)
 SELECT UPPER(TABLENAME) as TABLENAME,
        SUM(DATA_SIZE) AS DATA_SIZE,
        SUM(BELOW_250) AS BELOW_250,
        SUM(OVER_250) AS OVER_250
  FROM T1
 GROUP BY TABLENAME
 ORDER BY TABLENAME;
				  

     
    /* POSTGRESQL 인덱스 데이터 타입별 항목크기 산정 */ 
 with t1 as (
    SELECT A.TABLE_NAME ,
           INDEX_NAME,
	       A.COLUMN_NAME ,
	       A.DATA_TYPE ,
	       A.DATA_LENGTH ,
	       A.DATA_SIZE,
	       CASE WHEN A.DATA_SIZE < 250 THEN 1
	            ELSE 0
	       END BELOW_250,
	       CASE WHEN A.DATA_SIZE >= 250 THEN 1
	            ELSE 0
	       END OVER_250
      FROM (
            select TABLE_NAME , 
                   INDEX_NAME,
				   COLUMN_NAME,
				   DATA_TYPE,
				   DATA_LENGTH, 
				   ORDINAL_POSITION,
				   CASE WHEN DATA_TYPE = 'varchar' THEN CEIL(DATA_LENGTH * 0.7)
			            WHEN DATA_TYPE = 'numeric' THEN CEIL(DATA_LENGTH/2) + 1
			       ELSE DATA_LENGTH
			       END DATA_SIZE
             from 
             (   
				  SELECT TC.TABLE_NAME,
				         TC.CONSTRAINT_NAME AS INDEX_NAME,
				         COL.COLUMN_NAME,
				         COL.UDT_NAME AS DATA_TYPE,
				         COALESCE(COL.CHARACTER_MAXIMUM_LENGTH,COL.NUMERIC_PRECISION,COL.DATETIME_PRECISION,10) AS DATA_LENGTH, 
				         COL.ORDINAL_POSITION
				    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
				        ,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC
				        ,INFORMATION_SCHEMA.COLUMNS COL
				   WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
				     AND TC.constraint_schema  = 'naqsifh'
				     AND TC.TABLE_CATALOG   = CC.TABLE_CATALOG
				     AND TC.TABLE_SCHEMA    = CC.TABLE_SCHEMA
				     AND TC.TABLE_NAME      = CC.TABLE_NAME
				     AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
				     AND TC.TABLE_SCHEMA = COL.TABLE_SCHEMA
				     AND TC.TABLE_NAME = COL.TABLE_NAME
				     AND CC.column_name = COL.COLUMN_NAME
		    ) T
     ) A
	)
 SELECT upper(INDEX_NAME) AS INDEX_NAME,
        SUM(DATA_SIZE) AS DATA_SIZE,
        SUM(BELOW_250) AS BELOW_250,
        SUM(OVER_250) AS OVER_250
  FROM T1
 GROUP BY INDEX_NAME
  ORDER BY INDEX_NAME;