[postgresql] 메타데이터 & 자주쓰는 쿼리(정리중..)

lastUpdated: 2023.12.08

 

▼DB 용어

#DDL(Data Definition Language): CREATE/DROP, ALTER(스키마/도메인/테이블/뷰/인덱스를 정의/변경/제거 등)

#DML(Data Manipulation Language): INSERT/UPDATE/DELETE(데이터 관리)

#DCL(Data Control Language): COMMIT/ROLLBACK, GRANT/REVOKE (보안/무결성/회복/병행 제어 등을 정의)

 

▼SELECT

-- 변수 정의대신 CTE활용
WITH KEYS(ORD_ID, PAY_REQ_ID) --주문번호, 요청번호
  AS ( VALUES(
	  NULL --'20230905050132740'
    , NULL --'f0e3091d-8cd9-4186-b1c4-58859db39d7a'
    ) )
SELECT *
  FROM TBL1 T
 WHERE T.ORD_ID = (SELECT COALESCE(ORD_ID, T.ORD_ID) FROM KEYS) --◀
-- OR
WITH CTE AS (
  SELECT 'SYS-LOCAL' AS sys_id 
        ,'http://localhost:8081' AS sys_domn
)
SELECT sys_id, sys_domn
 FROM CTE;

 

▼메타데이터

-- 현재 database의 모든 테이블 목록 조회
select * from information_schema.tables where table_name;

-- 모든 database하의 모든 테이블 목록 조회
select * from pg_catalog.pg_tables 
 where tablename  like 'tbl_%' 
order by schemaname, tablename;

-- 테이블/컬럼 목록
   SELECT ps.schemaname, ps.relname as table_nm, obj_description(ps.relid)  as table_comment
		 ,pa.attname as column_nm, pd.description as column_comment
	 FROM pg_stat_all_tables ps
	 	  LEFT OUTER JOIN pg_description pd on ps.relid=pd.objoid
	 	  INNER JOIN pg_attribute pa on pd.objoid=pa.attrelid and pd.objsubid=pa.attnum
	WHERE 1=1
  	  AND pd.objsubid<>0
	  AND ps.schemaname='admdbt'
    --AND ps.relname='테이블'
 ORDER BY ps.relname, pd.objsubid;

▼DBMS 관련

-- DB서버 설정 리스트 조회
SHOW ALL;
SELECT * FROM pg_settings WHERE NAME LIKE '%port%';
SELECT * FROM pg_settings where name like '%version%'

-- 특정 설정값 조회
SELECT CURRENT_SETTING('TIMEZONE');
 │Asia/Seoul
SELECT CURRENT_SETTING('PORT')
 │5534
SHOW LC_COLLATE
 │en_US.UTF-8

▼테이블 관리(DML)

-- 컬럼 타입 변경
ALTER TABLE 테이블 ALTER COLUMN 컬럼 TYPE 타입;

-- FK로서 사용중인 컬럼을 포함한 PK 수정하기
ALTER TABLE 테이블 
  DROP CONSTRAINT PK제약ID CASCADE,
  ADD PRIMARY KEY (PK컬럼1, PK컬럼2, ..);

 

▼유틸성

-- Oracle의 NVL과 동일(컬럼값이 NULL이면 값 대체)
SELECT SUM(COALESCE(VAL1, 0)) AS TOT_SUM FROM table;

▼날짜시간 다루기

-- DB에는 UTC시각으로 저장됨. 조회시 표시는 설정된 timezone 기준으로 표시
SELECT NOW() AT TIME ZONE 'UTC' AS UTC기준시
	 , NOW() AT TIME ZONE 'CEST' AS CEST기준시각
	 , CURRENT_SETTING('TIMEZONE') AS TIMEZONE
	 , DATE_PART('hour', NOW() - NOW() AT TIME ZONE 'UTC') AS utc_offset
 │utc기준시	cest기준시각	timezone	utc_offset
 │2023-09-06 08:15:23.126	2023-09-06 10:15:23.126	Asia/Seoul	9
 
-- Timezone 정보 리스트
SELECT * FROM pg_timezone_names WHERE abbrev IN ('KST','CET','CEST','UTC') ORDER BY abbrev;
 │name			abbrev	utc_offset	is_dst
 │Asia/Seoul	KST		09:00:00	false
 │Europe/Berlin	CEST	02:00:00	true
 ...

 

▼트랜잭션 (출처: https://miniweb4u.tistory.com/188)

-- 실행중인 쿼리 목록 조회(for PID)
SELECT * FROM pg_stat_activity 
WHERE datname = 'db_name'
  AND state LIKE '%idle%'
  AND query LIKE '%alter%'

-- 실행중인 쿼리 취소
SELECT pg_cancel_backend(pid int);