[PostgreSQL] array_position 으로 상태값을 커스텀 순서로 정렬한다

설치·접속: PostgreSQL 설치와 접속

부제: 티켓 상태를 '접수 → 처리중 → 완료' 순으로 보여줘야 하는데 ORDER BY status 는 사전순으로 흩어질 때

CREATE TABLE ticket (id int, title text, status text);
-- '접수' '처리중' '완료' 가 섞여 들어있다

-- 사전순(원치 않음): '완료'가 맨 앞
SELECT id, status FROM ticket ORDER BY status, id;

-- array_position 으로 커스텀 순서: 배열에서의 위치를 정렬 키로
SELECT id, status,
       array_position(ARRAY['접수','처리중','완료'], status) AS ord
FROM ticket
ORDER BY array_position(ARRAY['접수','처리중','완료'], status), id;

-- ORDER BY status (사전순: 완료 < 접수 < 처리중)
 id | status 
----+--------
  1 | 완료
  6 | 완료
  2 | 접수
  4 | 접수
  3 | 처리중
  5 | 처리중

-- array_position 순서 (접수=1, 처리중=2, 완료=3)
 id | status | ord 
----+--------+-----
  2 | 접수   |   1
  4 | 접수   |   1
  3 | 처리중 |   2
  5 | 처리중 |   2
  1 | 완료   |   3
  6 | 완료   |   3

array_position(arr, value)는 배열 arr 안에서 value가 처음 나오는 위치(1부터)를 돌려준다. 상태값을 원하는 순서대로 나열한 배열 ['접수','처리중','완료']을 만들어 두고, 각 행의 status가 그 배열에서 몇 번째인지를 정렬 키로 쓰면 사전순이 아니라 업무 흐름 순서로 정렬된다. 별도 status_order 컬럼이나 코드 테이블을 안 만들어도, 정렬 규칙을 쿼리 한 줄에 인라인으로 박을 수 있다.

함정은 배열에 없는 값이다. array_position은 못 찾으면 NULL을 반환하고, ORDER BY에서 NULL은 기본적으로 맨 뒤(ASC 기준)로 간다. 상태값이 추가됐는데 정렬 배열을 안 고치면 그 행이 조용히 맨 끝으로 밀리니, 신규 상태는 NULLS LAST를 명시하거나 배열을 갱신해야 한다. 정렬을 자주 하고 데이터가 크면 이 표현식을 그대로 표현식 인덱스로 만들 수도 있다.

이렇게도 쓴다

없는 값을 뒤로 몰되 순서를 확정. (조합: NULLS LAST)

SELECT id, status FROM ticket
ORDER BY array_position(ARRAY['접수','처리중','완료'], status) NULLS LAST, id;

 

array_positions 는 값이 나오는 모든 위치를 배열로.

SELECT array_positions(ARRAY['a','b','a','c','a'], 'a');  -- {1,3,5}

 

정렬 표현식을 인덱스로 고정해 대량 정렬 가속. (조합: 표현식 인덱스)

CREATE INDEX idx_ticket_statusord
  ON ticket ((array_position(ARRAY['접수','처리중','완료'], status)));

 

요일 배열에서 위치 역산: 'mon' 은 몇 번째.

SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');  -- 2

 

원소 개수/길이 확인. (조합: cardinality)

SELECT cardinality(ARRAY['접수','처리중','완료']), array_length(ARRAY['접수','처리중','완료'], 1);

 

CASE 로 쓰던 정렬을 배열 한 줄로 대체(가독성).

-- 기존: ORDER BY CASE status WHEN '접수' THEN 1 WHEN '처리중' THEN 2 ELSE 3 END
-- 대체: ORDER BY array_position(ARRAY['접수','처리중','완료'], status)