로아

postgres 1분이상 실행중인 쿼리 검색

운영중에 DB가 느려지고 DB서버 CPU가 90%까지 치고 오르는 현상이 발생했다.

원인을 찾고자 느린 쿼리를 찾아서 튜닝을 하려고 현재 1분이상 실행중인 쿼리를 검색하는 SQL이다.

SELECT current_timestamp - query_start AS runtime, datname, usename, query, pid
FROM pg_stat_activity
WHERE state = 'active' AND current_timestamp - query_start > '30 sec'
ORDER BY 1 DESC;

postgresql 주말을 제외한 한 달의 모든 요일 얻기

select dt 
from generate_series(date'2019-11-01', date'2019-11-30', interval '1' day) as t(dt)
where extract(dow from dt) between 1 and 5;

https://stackoverflow.com/questions/59489628/get-all-days-in-a-month-excluding-weekends-postgresql

Postgresql 콤마로 구분된 데이터 열을 행으로 변환

SQL

with temp01 as (
	select 1 as  idx
		, '가,나,다' as col01
	union
	select 2 as  idx
		, '라,마,바' as col01
)
select idx,
       unnest(string_to_array(col01, ',')) as col01
from temp01
order by idx asc;

출처 : https://medium.com/swlh/three-routes-convert-comma-separated-column-to-rows-c17c85079ecf