test 테이블의 데이터

test1
abcd
1111
1234


array로 변경

select string_agg(test1, ',')
from test


{abcd,1111,1234}

string으로 변경

select string_agg(test1, ',')
from test


abcd,1111,1234


array 데이터를 각 로우로 변경

unnest(array타입)


-- 예시
select unnest(ARRAY[1,2])


1
2
(2 rows)

array 타입에 데이터 추가

array_append(array, 값)


-- 예시
array_append(ARRAY[1,2], 3)


{1,2,3}

array 타입끼리 협차기

array_cat(array, array}


-- 예시
array_cat(ARRAY[1,2], ARRAY[3,4])


{1,2,3,4}

array에서 string(단일컬럼)으로 변경

array_to_string(array, 구분자, [NULL 값 대체 구분자])


-- 예시
array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*')


1,2,3,*,5

string을 array로 변경

string_to_array(string, 구분자, [null로 변경할 값])


-- 예시
string_to_array('xx~^~yy~^~zz', '~^~', 'yy')
{xx,,zz}


정렬을 사용자가 원하는대로 하길 원하거나, WHERE절에 건 조건 그대로 정렬이 되기를 원할 때, 아래와 같은 방법으로 처리할 수 있습니다.


1. array_position 사용하기

PostgreSQL의 버전이 9.5 이상일 경우 array_position 함수를 사용하여 구현할 수 있습니다. 


SELECT *
FROM test 
WHERE pk IN (1, 3, 4, 6, 2) 
ORDER BY ARRAY_POSITION(ARRAY[1, 3, 4, 6, 2], pk)

위 구문을 사용할 때 주의할 점은 array에 들어가는 데이터 타입과 조회를 하고자 하는 컬럼의 타입이 동일해야 합니다.

  • ARRAY_POSITION(ARRAY[1::bigint, 3::bigint], 1::int)   - 에러


array_position(anyarray,anyelement [, int])란?
두 번째에 입력된 인수를 첫 번째에 입력된 인수(어레이)에서 검색을 하며 첫 번째로 나온 값을 반환합니다.
예시)
array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon')   – 결과 2

2. case ... when 사용하기

PostgreSQL의 버전이 9.5이하이거나 다른 DB를 사용할 경우 order by 에 case when 구문을 사용하여 구현할 수 있습니다.

SELECT * 
FROM test 
WHERE pk IN (1, 3, 4, 6, 2) 
ORDER BY 
CASE pk 
WHEN 1 THEN 0
WHEN 3 THEN 1
WHEN 4 THEN 2
WHEN 6 THEN 3
WHEN 2 THEN 4
ELSE 5
END


쿼리를 실행했다가 에러가 발생했다던지 너무 오래걸려 강제종료를 했다던지 등의 행동을 했는데 쿼리가 계속 돌고 있어서 테이블에 대한 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부터 시작)


+ Random Posts