[Postgresql] 유용한 쿼리

in Dev on Dev Note


작업하면서 유용한 쿼리 모음집 - 개인저장용

테이블별 rows count

// COUNT_TABLE
CREATE OR REPLACE FUNCTION COUNT_TABLE(tableNm TEXT)
    RETURNS INTEGER
    LANGUAGE PLPGSQL AS
$FUNC$
DECLARE
    V_TMP_QUERY varchar(100);
    ROWS  INTEGER;
BEGIN
    V_TMP_QUERY:= 'SELECT COUNT(*) FROM ' || tableNm;
    EXECUTE format(V_TMP_QUERY) into ROWS;
    RETURN ROWS;
END
$FUNC$;

 select * from (
        SELECT c.relname as "Name",
               pg_catalog.obj_description(c.oid, 'pg_class') as "Description",
               count_table( c.relname ) as "ROWS_CNT",
               TO_CHAR( pg_catalog.pg_table_size(c.oid)/1024 , '999,999,999,999,999') as "Size_MB"
        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
    )A
where "ROWS_CNT" > 0;

조회하고자 하는 테이블이 외래키로 참조하고 있는 테이블 및 컬럼 목록 조회

SELECT tc.constraint_type,CCU.table_name      AS search_table_name
     , CCU.column_name     AS search_column_name
     , KCU.table_name      AS foreign_table_name
     , KCU.column_name     AS foreign_column_name
     , KCU.constraint_name AS foreign_constraint_name
FROM information_schema.table_constraints TC
         JOIN information_schema.key_column_usage KCU ON TC.constraint_name = KCU.constraint_name
         JOIN information_schema.constraint_column_usage CCU ON CCU.constraint_name = TC.constraint_name
WHERE TC.constraint_type = 'FOREIGN KEY'
  AND CCU.table_name = 'ft_area_group';

조회하고자 하는 테이블을 외래키로 참조하고 있는 테이블 및 컬럼 목록 조회

SELECT TC.constraint_type,TC.table_name   AS search_table_name
     , KCU.column_name AS search_column_name
     , CCU.table_name  AS foreign_table_name
     , CCU.column_name AS foreign_column_name
     , CCU.constraint_name AS foreign_constraint_name
FROM information_schema.table_constraints TC
         JOIN information_schema.key_column_usage KCU ON KCU.constraint_name = TC.constraint_name
         JOIN information_schema.constraint_column_usage CCU ON CCU.constraint_name = TC.constraint_name
WHERE TC.constraint_type = 'FOREIGN KEY'
  AND TC.table_name = 'ft_store';

전체 테이블 컬럼 조회

SELECT TABLE_NAME as 테이블이름, column_name as 컬럼명,
       is_nullable as null가능여부,
       data_type as 데이터타입,
       character_maximum_length as 최대길이,
       character_octet_length,
       numeric_precision,
       numeric_precision_radix,
       numeric_scale
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'DB이름'
  and table_schema = 'public'(혹은 스키마 이름)
ORDER BY 테이블이름, ORDINAL_POSITION;

테이블 목록 조회

SELECT RELNAME AS TABLE_NAME
FROM PG_STAT_USER_TABLES;

컬럼 목록 조회

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = '데이터베이스명'
  AND TABLE_NAME    = '테이블명'
ORDER BY ORDINAL_POSITION;

테이블 COMMENT 조회

SELECT PS.RELNAME    AS TABLE_NAME
     ,PD.DESCRIPTION AS TABLE_COMMENT
FROM PG_STAT_USER_TABLES PS
   ,PG_DESCRIPTION      PD
WHERE PS.RELNAME  = '테이블명'
  AND PS.RELID   = PD.OBJOID
  AND PD.OBJSUBID  = 0;

컬럼 COMMENT 조회

SELECT PS.RELNAME    AS TABLE_NAME
     ,PA.ATTNAME     AS COLUMN_NAME
     ,PD.DESCRIPTION AS COLUMN_COMMENT
FROM PG_STAT_ALL_TABLES PS
   ,PG_DESCRIPTION     PD
   ,PG_ATTRIBUTE       PA
WHERE PS.SCHEMANAME = (SELECT SCHEMANAME
                       FROM PG_STAT_USER_TABLES
                       WHERE RELNAME = '테이블명')
  AND PS.RELNAME  = '테이블명'
  AND PS.RELID   = PD.OBJOID
  AND PD.OBJSUBID <> 0
  AND PD.OBJOID    = PA.ATTRELID
  AND PD.OBJSUBID  = PA.ATTNUM
ORDER BY PS.RELNAME, PD.OBJSUBID ;



© 2017. by isme2n