[PostgreSQL] multirange — 불연속 기간을 한 값으로 다룬다

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

부제: 강사의 주간 가용 시간이 오전·오후로 쪼개져 있는데, 예약 요청이 그 안에 통째로 들어가는지 한 번에 판정할 때

-- 겹치지 않는 여러 range 를 하나의 값으로: int4multirange
SELECT '{[9,12), [14,18)}'::int4multirange AS availability;

-- 예약 구간이 가용시간 안에 완전히 들어가는가 (@>)
SELECT '{[9,12), [14,18)}'::int4multirange @> '[10,11)'::int4range AS ok_morning,
       '{[9,12), [14,18)}'::int4multirange @> '[11,15)'::int4range AS spans_gap,
       '{[9,12), [14,18)}'::int4multirange @> '[15,17)'::int4range AS ok_afternoon;

   availability   
------------------
 {[9,12),[14,18)}

 ok_morning | spans_gap | ok_afternoon 
------------+-----------+--------------
 t          | f         | t

multirange는 서로 겹치지 않는 range들의 정렬된 집합을 한 값으로 담는 타입이다(int4multirange, tsmultirange, nummultirange, daterange용 datemultirange). 오전 [9,12)과 오후 [14,18)처럼 중간에 공백이 있는 불연속 가용시간을 배열이 아니라 전용 타입으로 저장하면, @>·&&·* 같은 범위 연산자가 그대로 먹는다. 예약 요청 [10,11)은 오전 조각 안에 들어가 참, [15,17)은 오후 조각 안이라 참, 그런데 점심 공백을 걸치는 [11,15)는 어느 조각에도 통째로 안 담겨 거짓이다. 이 "공백을 건너뛰는 판정"을 배열로 하려면 조각마다 루프를 돌아야 하지만, multirange는 @> 한 번이면 된다.

실제 강사 가용시간 테이블에 얹어 보면 예약 가능 강사만 골라낼 수 있다. tsmultirange로 요일·시간을 담고, 예약 요청 tsrange가 완전히 포함되는(@>) 강사를 찾는다.

CREATE TABLE instructor (id int, name text, available tsmultirange);
INSERT INTO instructor VALUES
 (1,'김강사','{[2026-07-20 09:00,2026-07-20 12:00), [2026-07-20 14:00,2026-07-20 18:00)}'),
 (2,'이강사','{[2026-07-20 13:00,2026-07-20 21:00)}');

-- 15:00~16:30 예약이 통째로 들어가는 강사
SELECT id, name FROM instructor
WHERE available @> '[2026-07-20 15:00,2026-07-20 16:30)'::tsrange;

-- 점심 공백(11:00~15:00)을 걸치는 요청은 아무도 못 받는다
SELECT id, name FROM instructor
WHERE available @> '[2026-07-20 11:00,2026-07-20 15:00)'::tsrange;

-- 15:00~16:30 (김강사 오후 조각 + 이강사 통구간, 둘 다 가능)
 id |  name  
----+--------
  1 | 김강사
  2 | 이강사

-- 11:00~15:00 (김강사는 점심 공백에 걸리고, 이강사는 시작 13:00 이전이 안 됨)
 id | name 
----+------
(0 rows)

김강사는 오후 조각 안에 15:0016:30이 들어가고, 이강사는 통으로 열린 시간이라 둘 다 잡힌다. 반면 점심을 걸치는 11:0015:00은 김강사의 공백에 막히고 이강사는 13:00부터라 시작을 못 담아, 결과가 0행이다. multirange가 PG14부터 추가된 비교적 신기능이라 아직 배열로 우회하는 코드가 많은데, "불연속 구간의 포함/겹침"이 도메인 로직이면 이 타입이 정답이다.

이렇게도 쓴다

&& 겹침: 요청이 가용시간과 조금이라도 겹치나.

SELECT '{[1,5), [10,15)}'::int4multirange && '[3,11)'::int4range AS overlaps;  -- t

 

* 교집합: 두 multirange의 겹치는 조각만.

SELECT '{[1,5), [10,15)}'::int4multirange * '{[3,12)}'::int4multirange;  -- {[3,5),[10,12)}

 

lower / upper 는 전체의 최소·최대 경계.

SELECT lower('{[1,5), [10,15)}'::int4multirange), upper('{[1,5), [10,15)}'::int4multirange);  -- 1, 15

 

여러 range를 하나의 multirange로 집계. (조합: range_agg)

SELECT range_agg(during) FROM (VALUES
  ('[9,12)'::int4range), ('[14,18)'::int4range)) v(during);

 

인접·겹치는 조각은 자동으로 합쳐진다(정규화).

SELECT '{[1,5), [5,9)}'::int4multirange;  -- {[1,9)} 로 병합

 

multirange를 개별 range 행으로 펼치기. (조합: unnest)

SELECT unnest('{[9,12), [14,18)}'::int4multirange);