📌 PostgreSQL 자주 쓰는 Query 정리

-- schema 별 relation (table, view 등) 조회
SELECT relnamespace::regnamespace, relname, relkind 
  FROM pg_class 
 WHERE relnamespace::regnamespace::text like '%_%' ;

-- 실행중인 쿼리 확인
SELECT pid, query_start, query, state 
  FROM pg_catalog.pg_stat_activity 
 WHERE current_timestamp - query_start > '1 sec' and state <> 'idle';

-- 실행중인 쿼리 중단
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);

-- user login password 생성
CREATE USER username WITH login password 'loginpassword';

-- schema 별 용량 조회
SELECT schema_name,
    pg_size_pretty(sum(table_size)::bigint) AS "disk space",
    pg_size_pretty(pg_database_size(current_database())) AS "DB_size",
    (sum(table_size) / pg_database_size(current_database())) * 100
        AS "percent (%)"
FROM (
     SELECT pg_catalog.pg_namespace.nspname AS schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size
       FROM   pg_catalog.pg_class
       JOIN pg_catalog.pg_namespace
         ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name;

-- table 목록 조회
SELECT RELNAME AS TABLE_NAME
  FROM PG_STAT_USER_TABLES;

-- column 목록 조회
SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_CATALOG = '데이터베이스명'
   AND TABLE_NAME    = '테이블명'
 ORDER BY ORDINAL_POSITION;
 
-- 기본키 조회
SELECT CC.COLUMN_NAME AS COLUMN_NAME
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS       TC
      ,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC
 WHERE TC.TABLE_CATALOG   = '데이터베이스명'
   AND TC.TABLE_NAME      = '테이블명'
   AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
   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;
 
 -- table 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;
   
-- column 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;