PostgreSQL의 모든 타입은 배열이 될 수 있습니다. 컬럼을 text[]나 int[]로 선언하고, 값은 '{a,b}'나 ARRAY['a', 'b']로 쓰고, 요소는 tags[1]로 읽습니다(인덱스는 1부터 시작). 배열에 어떤 값이 들어 있는지 확인하려면 'value' = ANY(tags)나 tags @> ARRAY['value']를 쓰세요.
게시물 1과 2에 sql 태그가 있습니다. tags[1]은 첫 번째 요소이고 cardinality는 요소 수를 셉니다. 두 리터럴 형태는 같은 값을 저장합니다. 요소가 식에서 나오거나 쉼표가 들어 있을 때는 ARRAY[...]가 더 편합니다.
배열에 값이 있는지 확인하기
포함 여부는 연산자 네 개로 다룹니다:
| 검사 | 의미 | '{sql,json}'에서 참인가 |
|---|---|---|
'sql' = ANY(tags) | 값 하나가 배열 안에 있음 | 예 |
tags @> '{sql,json}' | 배열이 이 값들을 모두 포함함 | 예 |
tags <@ '{sql,json,go}' | 모든 요소가 이 목록 안에 있음 | 예 |
tags && '{go,json}' | 두 배열에 공통 요소가 하나 이상 있음 | 예 |
두 태그를 모두 가진 것은 게시물 1뿐입니다. 게시물 2와 3은 {go,json}과 공통 요소가 있습니다. no_go가 거짓인 것은 게시물 3뿐입니다. ALL은 ANY의 짝으로, 모든 요소에 대해 비교가 성립해야 합니다.
= ANY는 SQL의 NULL 규칙을 따릅니다. 값을 찾지 못했는데 배열에 NULL이 있으면 결과는 거짓이 아니라 NULL입니다. 그래서 WHERE NOT ('x' = ANY(tags))는 기대한 행을 빠뜨릴 수 있습니다. @>와 &&는 참이나 거짓을 돌려줍니다.
GIN 인덱스를 두면 큰 테이블에서 @>, <@, &&, =가 빨라집니다. = ANY(column)에는 도움이 안 되므로, 인덱스를 쓰는 조회는 tags @> ARRAY['sql']로 쓰세요:
CREATE INDEX posts_tags_idx ON posts USING gin (tags);
unnest로 배열을 행으로 펼치기
unnest는 요소마다 한 행을 돌려주므로, 요소 단위로 세고, 조인하고, 거를 수 있습니다:
sql은 게시물 두 개에, 나머지 태그는 하나씩에 나옵니다. WITH ORDINALITY(unnest(tags) WITH ORDINALITY AS t(tag, pos))를 붙이면 각 요소의 위치도 얻습니다. 행을 다시 배열로 모으려면 array_agg를 쓰세요(string_agg와 array_agg 참고).
요소 추가, 삭제, 변경하기
결과는 {1,2,3}, {1,2,3,4}, {2,3}, {1,9,3}, 2, {20,30}, a, b, c입니다. UPDATE에서도 같은 함수로 저장된 배열을 바꿉니다: SET tags = array_append(tags, 'new') 또는 SET tags = array_remove(tags, 'old'). array_remove는 그 값을 모두 지웁니다. string_to_array('a,b,c', ',')는 텍스트를 배열로 나눕니다.
배열 길이 구하기
cardinality(tags)는 요소 수를 돌려주며, 빈 배열이면 0입니다. array_length(tags, 1)은 1차원의 길이를 돌려주며, 빈 배열이면 NULL입니다. 그래서 WHERE array_length(tags, 1) = 0은 절대 맞지 않습니다. 빈 배열은 cardinality(tags) = 0이나 tags = '{}'로 검사하세요.
PostgreSQL 배열은 여러 차원(int[][])을 가질 수 있지만, 하위 배열의 길이가 모두 같아야 하고, 선언한 크기(int[3])는 강제되지 않습니다. 중첩되거나 길이가 제각각인 데이터에는 보통 JSONB가 더 잘 맞습니다.
자주 묻는 질문
PostgreSQL 배열 인덱스는 0부터인가요, 1부터인가요?
기본은 1부터입니다. tags[1]이 첫 번째 요소입니다. 끝을 넘는 인덱스는 오류 대신 NULL을 돌려줍니다.
PostgreSQL에서 IN과 = ANY는 무엇이 다른가요?
x IN (1, 2, 3)은 쿼리에 직접 쓴 목록을 받고, x = ANY(array)는 배열 값 하나를 받습니다. 애플리케이션 코드에는 ANY가 잘 맞습니다. 플레이스홀더 목록을 만들 필요 없이 배열 파라미터 하나만 넘기면 되기 때문입니다(WHERE id = ANY($1)).
배열 컬럼을 써야 하나요, 별도 테이블을 써야 하나요?
태그처럼 행과 함께 읽고 쓰는 짧은 목록에는 배열이 맞습니다. 항목마다 컬럼이나 외래 키가 필요하거나 항목을 하나씩 업데이트한다면, 항목당 한 행을 두는 별도 테이블이 조회하기도, 제약 조건을 걸기도 쉽습니다.
PostgreSQL에서 배열이 비었는지 어떻게 확인하나요?
cardinality(arr) = 0이나 arr = '{}'를 쓰세요. array_length는 빈 배열에 NULL을 돌려주므로 array_length(arr, 1) = 0은 절대 맞지 않습니다.