PostgreSQL 쿼리

-- 테이블 명세 조회

SELECT
    c.relname AS table_name,
    pg_catalog.obj_description(c.oid, 'pg_class') AS table_comment,
    a.attname AS column_name,
    col_description(a.attrelid, a.attnum) AS column_comment,
    CASE WHEN pg_catalog.format_type(a.atttypid, a.atttypmod) LIKE 'timestamp%' THEN 'timestamp'
--         WHEN pg_catalog.format_type(a.atttypid, a.atttypmod) LIKE 'character varying%' THEN ''
         ELSE REPLACE(pg_catalog.format_type(a.atttypid, a.atttypmod), 'character varying', 'varchar')
    END AS column_type,
    CASE WHEN a.attnotnull THEN 'Y' ELSE 'N' END AS is_not_null
--    t.typname AS column_type,
--    CASE 
--        WHEN a.atttypmod > 0 THEN a.atttypmod - 4
--        ELSE NULL
--    END AS column_length,
FROM
    pg_catalog.pg_namespace n
    JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
    JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
--    JOIN pg_catalog.pg_type t ON a.atttypid = t.oid
WHERE
    a.attnum > 0
    AND NOT a.attisdropped
    AND c.relkind = 'r'  -- 테이블만 선택
    AND c.relname = lower('OP_ORD_DTL_GOODS_INFO_PRCE_HIST')
    AND n.nspname = 'mn_mgr'  -- 필요한 스키마로 변경 가능
ORDER BY
    table_name, a.attnum;

 

-- 테이블 코멘트 조회

SELECT N.NSPNAME
     , C.RELNAME
     , OBJ_DESCRIPTION(C.OID, 'pg_class') 
FROM   PG_CATALOG.PG_CLASS C INNER JOIN 
       PG_CATALOG.PG_NAMESPACE N 
ON     C.RELNAMESPACE = N.OID 
WHERE  1 = 1
and    C.RELKIND = 'r'
AND    NSPNAME = 'public'
AND    RELNAME = '테이블명'

-- 컬럼 코멘트 조회

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  PD.OBJSUBID <> 0
AND    PS.RELID = PD.OBJOID
AND    PD.OBJOID = PA.ATTRELID
AND    PD.OBJSUBID = PA.ATTNUM
AND    PS.SCHEMANAME = 'public'
AND    PS.RELNAME='테이블명'
ORDER BY PS.RELNAME
       , PD.OBJSUBID

 

-- 컬럼 데이터 타입 조회

SELECT PS.RELNAME AS TABLE_NAME
     , (SELECT OBJ_DESCRIPTION(C.OID, 'pg_class') 
        FROM   PG_CATALOG.PG_CLASS C INNER JOIN 
               PG_CATALOG.PG_NAMESPACE N 
        ON     C.RELNAMESPACE = N.OID 
        WHERE  1 = 1
        and    C.RELKIND = 'r'
        AND    NSPNAME = PS.SCHEMANAME
        AND    RELNAME = PS.RELNAME) AS TABLE_COMMENT
     , PA.ATTNAME AS COLUMN_NAME
     , PD.DESCRIPTION AS COLUMN_COMMENT
     , CASE WHEN COL.udt_name = 'varchar' THEN concat(COL.udt_name, '(', COL.character_maximum_length, ')')
            WHEN COL.udt_name = 'numeric' THEN concat(COL.udt_name, '(', COL.numeric_precision, ')')
            ELSE COL.udt_name end
     , CASE WHEN COL.is_nullable = 'NO' THEN 'Y' ELSE 'N' end AS not_null
FROM   PG_STAT_ALL_TABLES PS
     , PG_DESCRIPTION PD
     , PG_ATTRIBUTE PA
     , information_schema.COLUMNS COL
WHERE  PD.OBJSUBID <> 0
AND    PS.RELID = PD.OBJOID
AND    PD.OBJOID = PA.ATTRELID
AND    PD.OBJSUBID = PA.ATTNUM
AND    PS.SCHEMANAME = COL.table_schema
AND    PS.RELNAME = COL.table_name
AND    PA.ATTNAME = COL.column_name
AND    PS.SCHEMANAME = 'mn_mgr'
AND    PS.RELNAME='pr_goods_base_ml'
ORDER BY PS.RELNAME
       , PD.OBJSUBID

 

-- 특정 컬럼명을 사용하는 테이블 찾기

SELECT psat.relname
     , pd.description
FROM   PG_ATTRIBUTE pa
     , PG_DESCRIPTION pd
     , PG_STAT_ALL_TABLES psat
WHERE  1 = 1
AND    pa.attrelid = pd.objoid
AND    pd.objsubid = 0  -- (0:테이블, 1이상:컬럼)
AND    pd.objoid = psat.relid
AND    pa.attname = '컬럼명'
ORDER BY psat.relname

-- 특정 컬럼이 있는 테이블의 특정 컬럼 사이즈 변경

DO
$$
DECLARE
    v_schema varchar(10) := 'public';
    v_col_name varchar(10) := 'mbr_no';  -- 변경할 컬럼명
    v_col_type varchar(10) := 'varchar';  -- 변경할 컬럼 타입
    v_col_size varchar(10) := '20'; -- 변경할 컬럼 사이즈
    v_sql varchar(1000) := 'aaa';
    rslt record;
BEGIN
	FOR rslt IN 
	(
		SELECT psat.relname
		     , pd.description
		     , psat.schemaname
		FROM   PG_ATTRIBUTE pa
		     , PG_DESCRIPTION pd
		     , PG_STAT_ALL_TABLES psat
		WHERE  1 = 1
		AND    pa.attrelid = pd.objoid
		AND    pd.objsubid = 0  -- (0:테이블, 1이상:컬럼)
		AND    pd.objoid = psat.relid
		AND    psat.schemaname = v_schema
--		AND    psat.relname = 'pr_sns_stylr_rnk'
		AND    pa.attname = v_col_name
		ORDER BY psat.relname
	)
	LOOP
--	    RAISE info 'table_name : %', rslt.relname;
	   v_sql := 'ALTER TABLE ' || rslt.schemaname || '.' || rslt.relname || ' ALTER COLUMN ' || v_col_name || ' TYPE ' || v_col_type || '(' || v_col_size || ') USING ' || v_col_name || '::' || v_col_type;
	   raise info 'v_sql : %', v_sql;
       EXECUTE v_sql;
	END LOOP;
END;
$$

-- 테이블 권한 조회

SELECT *
FROM   information_schema.role_table_grants 
WHERE  table_name = 'tab'  -- 테이블명

-- 실행중인 쿼리 조회

select * from pg_stat_activity;

-- lock 조회

select * from pg_locks;

-- 인덱스 조회 및 생성

-- 인덱스 조회
SELECT *
FROM   pg_indexes
WHERE  1 = 1
AND    tablename = 'op_claim_base_tmp'
AND    schemaname = 'op_adm'
;

-- 인덱스 생성
CREATE INDEX idx_op_claim_base_tmp ON op_adm.op_claim_base_tmp USING btree (claim_dtm);