쿼리를 실행했다가 에러가 발생했다던지 너무 오래걸려 강제종료를 했다던지 등의 행동을 했는데 쿼리가 계속 돌고 있어서 테이블에 대한 transaction lock이 걸린 경우, 실행 중인 쿼리를 종료해야 할 때 유용하게 쓰입니다.

현재 실행 중인 쿼리 및 pid 확인

SELECT * FROM pg_stat_activity ORDER BY query_start ASC;

실행 중인 쿼리 종료

SELECT pg_cancel_backend(pid값); -- 성공하면 true, 실패하면 false 반환


검색하고자 하는 테이블을 참조하고 있는 테이블 및 컬럼 리스트 확인

SELECT kcu.table_name AS child_table,
	kcu.table_schema AS child_schema,
    kcu.column_name AS child_column,
    kcu.constraint_name AS child_constraint
 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 = '테이블명';
GROUP BY kcu.table_name, kcu.table_schema, kcu.column_name, kcu.constraint_name;

현재 테이블이 참조하고 있는 테이블 및 컬럼 확인

 SELECT tc.table_name AS child_table,
    kcu.column_name AS child_column,
    ccu.table_name AS foreign_table,
    ccu.column_name AS foreign_column
   FROM information_schema.table_constraints tc
     JOIN information_schema.key_column_usage kcu ON tc.constraint_name::text = kcu.constraint_name::text
     JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name::text = tc.constraint_name::text
  WHERE tc.constraint_type = 'FOREIGN KEY' and tc.table_name = '테이블명';


위의 FOREIGN KEY 대신 PRIMARY KEY 라던지 제약조건을 작성하면 해당하는 결과가 떨어짐


-- split_part(컬럼,자르고자하는 문자, 인덱스)


-- ex
select split_part(tel,'-',4)
from test
-- test 테이블의 tel 컬럼에서 - 문자로 자른 후 4번째에 있는 데이터를 출력 (index는 0이 아닌 1부터 시작)



-- (length(특정컬럼) - length(replace(특정컬럼, 찾고자 하는 문자 , ''))) = 찾고자 하는 개수


-- ex)
select *
from test
where (length(tel) - length(replace(tel, '-' , ''))) = 3
-- test테이블 내 tel 컬럼에서 - 값이 3개인 값


  1. 2017.09.21 15:32

    비밀댓글입니다

PostgreSQL 및 여러 DB 가상 실행: http://sqlfiddle.com/

왼쪽의 build schema를 누르면 작성된 내용대로 스키마가 생성되고 오른쪽의 run sql을 누르면 작성한 sql이 실행되어 하단에 결과 테이블이 출력됩니다.

예제 템플릿 : http://sqlfiddle.com/#!15/58d92/1



컬럼을 지정한 다음 중복된 데이터라면 그 이전 데이터의 값과 다음 데이터의 값을 확인하려고 할 때 사용할 수 있습니다.

테스트 데이터

먼저 테스트 대상 테이블입니다.

Client | Rate | StartDate|EndDate     
 A      | 1000 | 2005-1-1 |2005-12-31
 A      | 2000 | 2006-1-1 |2006-12-31
 A      | 3000 | 2007-1-1 |2007-12-31  
 B      | 5000 | 2006-1-1 |2006-12-31  
 B      | 8000 | 2008-1-1 |2008-12-31  
 C      | 2000 | 2006-1-1 |2006-12-31  


다음은 원하고자 하는 결과 테이블입니다.

Client | Rate | StartDate|EndDate    |Pre Rate | Pre StartDate |Pre EndDate    
 A      | 1000 | 2005-1-1 |2005-12-31 |         |               |             
 A      | 2000 | 2006-1-1 |2006-12-31 | 1000    | 2005-1-1      |2005-12-31            
 A      | 3000 | 2007-1-1 |2007-12-31 | 2000    | 2006-1-1      |2006-12-31  
 B      | 5000 | 2006-1-1 |2006-12-31 |         |               |              
 B      | 8000 | 2008-1-1 |2008-12-31 | 5000    | 2006-1-1      |2006-12-31   
 C      | 2000 | 2006-1-1 |2006-12-31 

결과

window function을 사용하여 아래와 같이 작성합니다.

SELECT client, 
       rate,
       startdate, 
       enddate, 
       lag(rate) over client_window as pre_rate,
       lag(startdate) over client_window as pre_startdate,
       lag(enddate) over client_window as pre_enddate
FROM the_table
WINDOW client_window as (partition by client order by startdate)
ORDER BY client, stardate;

해당 결과는 이전 데이터만 확인하는 lag() 함수를 사용했습니다. 만약 다음 데이터를 확인하고 싶은 경우엔 lead()함수를 사용하면 됩니다.

응용

이전 데이터 뿐만 아니라, 이전전의 데이터와 다음다음의 데이터를 확인하고 싶으면 아래와 같이 작성합니다. (위의 테스트 테이블과 별개의 쿼리)

select 
    *,
    lag(previous_id) over w as pre_previous_id, 
    lag(previous_title) over w as pre_previous_title, 
    lead(next_id) over w as next_next_id, 
    lead(next_title) over w as next_next_title
from (
    select 
        *,
        lag(id) over w as previous_id, 
        lag(title) over w as previous_title, 
        lead(id) over w as next_id, 
        lead(title) over w as next_title
    from 
        the_table
    
    window w as (partition by category order by insert_time)
) as onestep_table
window w as (partition by category order by insert_time)


+ Random Posts