배열
배열 (Arrays)
여러 값을 하나의 컬럼에 담아야 할 때가 있어요. 예를 들어 사원의 분기별 연봉을 나란히 저장하거나, 한 주의 일정을 요일별로 담아두는 식이죠. 그럴 때 옆에 여러 컬럼을 나열하는 대신, PostgreSQL의 배열(array) 을 쓰면 하나의 컬럼에 '가변 길이의 다차원 배열'을 저장할 수 있어요. 배열은 내장 타입이든 사용자 정의 기본 타입이든, enum, 복합, 범위 타입, 도메인까지 어떤 기반 타입으로든 만들 수 있어요.
출처: 공식문서
배열 타입 선언하기
배열 타입은 요소 타입 이름 뒤에 대괄호 []를 붙여서 표현해요. 예시로 sal_emp 테이블을 만들어 볼게요.
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
name은text타입이에요.pay_by_quarter는 1차원integer배열로, 사원의 분기별 연봉을 담아요.schedule은 2차원text배열로, 사원의 주간 일정을 담아요.
CREATE TABLE 문법은 배열의 정확한 크기도 지정할 수 있게 해줘요.
CREATE TABLE tictactoe (
squares integer[3][3]
);
하지만 현재 구현은 지정한 배열 크기 제한을 무시해요. 즉 크기를 정하지 않은 배열과 동일하게 동작하죠. 선언된 차원 수(dimension)도 강제하지 않아요. 특정 요소 타입의 배열은 크기나 차원 수와 무관하게 전부 같은 타입으로 취급돼요. 그래서 CREATE TABLE에 배열 크기나 차원 수를 적어두는 건 단순한 문서화일 뿐, 실행 시 동작에는 영향을 주지 않아요.
1차원 배열에는 SQL 표준에 맞는 ARRAY 키워드 문법도 쓸 수 있어요. pay_by_quarter를 이렇게 정의할 수도 있었죠.
pay_by_quarter integer ARRAY[4],
크기를 지정하지 않으려면:
pay_by_quarter integer ARRAY,
어느 경우든 PostgreSQL은 크기 제한을 강제하지 않아요.
배열 값 입력하기
배열 값을 리터럴로 쓸 때는 중괄호로 요소를 감싸고 쉼표로 구분해요. (C 언어를 안다면 구조체 초기화 문법과 비슷하다고 느낄 거예요.) 요소 값에 큰따옴표를 붙일 수 있고, 값이 쉼표나 중괄호를 포함하면 반드시 붙여야 해요. 배열 상수의 일반적인 형식은 이래요.
'{ val1 delim val2 delim ... }'
여기서 delim은 해당 타입의 구분 문자로, pg_type 엔트리에 기록돼 있어요. PostgreSQL 기본 배포판의 표준 타입들은 모두 쉼표(,)를 쓰는데, box 타입만 세미콜론(;)을 써요. 각 val은 배열 요소 타입의 상수이거나 부분 배열(subarray)이에요. 배열 상수의 예는 이래요.
'{{1,2,3},{4,5,6},{7,8,9}}'
이 상수는 3x3 2차원 배열로, 정수 부분 배열 세 개로 구성돼요.
배열 상수의 요소를 NULL로 설정하려면 요소 값에 NULL을 쓰면 돼요. (대소문자 구분 없이 NULL의 어떤 변형이든 됩니다.) 실제 문자열 "NULL"을 넣고 싶다면 큰따옴표로 감싸야 해요.
(이런 배열 상수는 사실 일반 타입 상수의 특수한 경우에 불과해요. 상수는 처음에 문자열로 취급되어 배열 입력 변환 루틴에 전달돼요. 명시적 타입 지정이 필요할 수도 있어요.)
이제 몇 가지 INSERT 문을 볼게요.
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
VALUES ('Carol',
'{20000, 25000, 25000, 25000}',
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
앞의 두 INSERT 결과는 이렇게 보여요.
SELECT * FROM sal_emp;
name | pay_by_quarter | schedule
-------+---------------------------+-------------------------------------------
Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)
다차원 배열은 각 차원의 범위(extent)가 서로 맞아야 해요. 안 맞으면 오류가 나요.
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"meeting"}}');
ERROR: malformed array literal: "{{"meeting", "lunch"}, {"meeting"}}"
DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions.
ARRAY 생성자 문법도 쓸 수 있어요.
INSERT INTO sal_emp
VALUES ('Bill',
ARRAY[10000, 10000, 10000, 10000],
ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
INSERT INTO sal_emp
VALUES ('Carol',
ARRAY[20000, 25000, 25000, 25000],
ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
배열 요소는 평범한 SQL 상수나 표현식이에요. 예를 들어 문자열 리터럴은 배열 리터럴에서처럼 큰따옴표가 아니라 작은따옴표로 감싸요.
배열 접근하기
이제 테이블 쿼리를 해볼게요. 먼저 배열의 한 요소에 접근하는 방법이에요. 2분기에 연봉이 바뀐 사원의 이름을 찾는 쿼리예요.
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
name
-------
Carol
(1 row)
배열 첨자(subscript) 번호는 대괄호 안에 써요. 기본적으로 PostgreSQL은 배열 첨자를 1부터 시작해요. 즉 n개 요소의 배열은 array[1]부터 array[n]까지예요.
전 사원의 3분기 연봉을 찾는 쿼리:
SELECT pay_by_quarter[3] FROM sal_emp;
pay_by_quarter
----------------
10000
25000
(2 rows)
배열의 임의의 직사각형 조각(slice), 즉 부분 배열에도 접근할 수 있어요. 배열 슬라이스는 한 개 이상의 차원에 대해 하한:상한을 적는 방식으로 표기해요. Bill의 주간 일정 중 처음 이틀의 첫 항목을 찾는 쿼리예요.
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
schedule
------------------------
{{meeting},{training}}
(1 row)
어떤 차원이든 콜론이 들어간 슬라이스로 쓰면, 모든 차원이 슬라이스로 취급돼요. 숫자만 쓰인(콜론이 없는) 차원은 1부터 그 숫자까지로 취급해요. 예를 들어 [2]는 [1:2]로 다뤄져요.
SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
schedule
-------------------------------------------
{{meeting,lunch},{training,presentation}}
(1 row)
비슬라이스 경우와의 혼동을 피하려면 모든 차원에 슬라이스 문법을 쓰는 게 좋아요. 즉 [2][1:1]보다는 [1:2][1:1]처럼요.
슬라이스 지정자의 하한이나 상한을 생략할 수 있어요. 생략된 경계는 배열 첨자의 하한이나 상한으로 대체돼요.
SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';
schedule
------------------------
{{lunch},{presentation}}
(1 row)
SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';
schedule
------------------------
{{meeting},{training}}
(1 row)
배열 자체나 첨자 표현식이 NULL이면 배열 첨자 표현식은 NULL을 반환해요. 첨자가 배열 범위를 벗어나도(이 경우 오류가 아니라) NULL이 반환돼요. 예를 들어 schedule이 현재 [1:3][1:2] 차원이라면 schedule[3][3]은 NULL을 내놓아요. 마찬가지로 첨자의 개수가 틀린 배열 참조도 오류가 아니라 NULL을 내놓아요.
배열 슬라이스 표현식도 배열이나 첨자 표현식이 NULL이면 NULL을 내놓아요. 하지만 배열 현재 범위를 완전히 벗어나는 슬라이스처럼 다른 경우에는 NULL 대신 빈(0차원) 배열을 내놓아요. (이건 비슬라이스 동작과 맞지 않지만 역사적 이유로 그렇게 돼 있어요.) 요청한 슬라이스가 배열 범위와 부분적으로 겹치면, NULL을 돌려주는 대신 조용히 겹치는 영역으로 줄어들어요.
배열 값의 현재 차원은 array_dims 함수로 얻을 수 있어요.
SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
array_dims
------------
[1:2][1:2]
(1 row)
array_dims는 사람이 읽기 편한 text 결과를 주지만 프로그램에는 불편할 수 있어요. 차원은 array_upper와 array_lower로도 얻을 수 있는데, 각각 지정된 차원의 상한과 하한을 반환해요.
SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
array_upper
-------------
2
(1 row)
array_length는 지정된 배열 차원의 길이를 반환해요.
SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
array_length
--------------
2
(1 row)
cardinality는 모든 차원을 통틀어 배열의 총 요소 수를 반환해요. 실질적으로 unnest 호출이 내놓을 행 수와 같아요.
SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';
cardinality
-------------
4
(1 row)
배열 수정하기
배열 값을 통째로 교체할 수 있어요.
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Carol';
ARRAY 표현식 문법으로도:
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Carol';
한 요소만 갱신할 수도 있어요.
UPDATE sal_emp SET pay_by_quarter[4] = 15000
WHERE name = 'Bill';
슬라이스 단위로도:
UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
WHERE name = 'Carol';
하한이나 상한을 생략한 슬라이스 문법도 쓸 수 있는데, 다만 NULL이 아니고 0차원도 아닌 배열 값을 갱신할 때만 가능해요 (그렇지 않으면 대체할 기존 첨자 한계가 없거든요).
아직 존재하지 않는 요소에 값을 할당하면 저장된 배열 값을 확장할 수 있어요. 기존 요소와 새로 할당한 요소 사이의 위치들은 NULL로 채워져요. 예를 들어 myarray가 현재 4개 요소라면, myarray[6]에 할당하는 갱신 뒤에는 6개 요소가 되고 myarray[5]는 NULL이 돼요. 이런 확장은 현재 1차원 배열에서만 허용되고 다차원 배열은 안 돼요.
첨자 할당을 쓰면 1부터 시작하지 않는 배열도 만들 수 있어요. 예를 들어 myarray[-2:7]에 할당하면 -2부터 7까지의 첨자 값을 가진 배열이 만들어져요.
새 배열 값은 연결 연산자 ||로도 만들 수 있어요.
SELECT ARRAY[1,2] || ARRAY[3,4];
?column?
-----------
{1,2,3,4}
(1 row)
SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
?column?
---------------------
{{5,6},{1,2},{3,4}}
(1 row)
연결 연산자는 1차원 배열의 앞이나 뒤에 단일 요소를 붙일 수 있어요. 또 N차원 배열 두 개, 혹은 N차원과 N+1차원 배열을 받아들이기도 해요.
1차원 배열의 앞이나 뒤에 단일 요소를 붙이면, 결과는 배열 피연산자와 같은 하한 첨자를 갖는 배열이 돼요.
SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
array_dims
------------
[0:2]
(1 row)
SELECT array_dims(ARRAY[1,2] || 3);
array_dims
------------
[1:3]
(1 row)
차원 수가 같은 배열 두 개를 연결하면, 결과는 왼쪽 피연산자 바깥 차원의 하한 첨자를 유지해요. 결과는 왼쪽 피연산자의 모든 요소 다음에 오른쪽 피연산자의 모든 요소가 이어지는 배열이에요.
SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
array_dims
------------
[1:5]
(1 row)
SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
array_dims
------------
[1:5][1:2]
(1 row)
N차원 배열을 N+1차원 배열의 앞이나 뒤에 붙이면, 결과는 위의 요소-배열 경우와 유사해요. 각 N차원 부분 배열은 사실상 N+1차원 배열 바깥 차원의 요소죠.
SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
array_dims
------------
[1:3][1:2]
(1 row)
배열은 array_prepend, array_append, array_cat 함수로도 만들 수 있어요. 처음 두 함수는 1차원 배열만 지원하고, array_cat은 다차원 배열을 지원해요.
SELECT array_prepend(1, ARRAY[2,3]);
array_prepend
---------------
{1,2,3}
(1 row)
SELECT array_append(ARRAY[1,2], 3);
array_append
--------------
{1,2,3}
(1 row)
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
array_cat
-----------
{1,2,3,4}
(1 row)
SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
array_cat
---------------------
{{1,2},{3,4},{5,6}}
(1 row)
SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
array_cat
---------------------
{{5,6},{1,2},{3,4}}
단순한 경우에는 앞서 살펴본 연결 연산자가 이 함수들을 직접 쓰는 것보다 선호돼요. 하지만 연결 연산자는 세 경우 모두에 과부하(overload)되어 있어서, 모호함을 피하려고 함수 하나를 쓰는 편이 도움이 되는 상황이 있어요.
SELECT ARRAY[1, 2] || '{3, 4}'; -- the untyped literal is taken as an array
?column?
-----------
{1,2,3,4}
SELECT ARRAY[1, 2] || '7'; -- so is this one
ERROR: malformed array literal: "7"
SELECT ARRAY[1, 2] || NULL; -- so is an undecorated NULL
?column?
----------
{1,2}
(1 row)
SELECT array_append(ARRAY[1, 2], NULL); -- this might have been meant
array_append
--------------
{1,2,NULL}
위 예시에서 파서는 연결 연산자 한쪽에 정수 배열, 다른 쪽에 타입이 결정되지 않은 상수를 봐요. 파서가 그 상수의 타입을 결정하는 데 쓰는 휴리스틱은, 연산자의 다른 입력과 같은 타입이라고 가정하는 것 — 이 경우 정수 배열이에요. 그래서 연결 연산자는 array_append가 아니라 array_cat을 나타내는 것으로 간주돼요. 그 선택이 틀렸다면 상수를 배열 요소 타입으로 캐스팅해 고칠 수 있는데, 명시적으로 array_append를 쓰는 편이 더 나은 해결책일 수 있어요.
배열에서 값 찾기
배열에서 값을 찾으려면 각 값을 검사해야 해요. 배열 크기를 안다면 수동으로 할 수 있어요.
SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
pay_by_quarter[2] = 10000 OR
pay_by_quarter[3] = 10000 OR
pay_by_quarter[4] = 10000;
하지만 큰 배열에선 금방 지루해지고, 배열 크기를 모르면 쓸모가 없어요. 대안적인 방법이 있는데, 위 쿼리는 이렇게 바꿀 수 있어요.
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
배열의 모든 값이 10000인 행을 찾으려면:
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
generate_subscripts 함수를 쓸 수도 있어요.
SELECT * FROM
(SELECT pay_by_quarter,
generate_subscripts(pay_by_quarter, 1) AS s
FROM sal_emp) AS foo
WHERE pay_by_quarter[s] = 10000;
또한 && 연산자로 배열을 검색할 수 있는데, 이 연산자는 왼쪽 피연산자가 오른쪽 피연산자와 겹치는지 확인해요.
SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];
array_position과 array_positions 함수로도 배열에서 특정 값을 찾을 수 있어요. 전자는 값이 배열에서 처음 나타나는 첨자를 반환하고, 후자는 값이 나타나는 모든 첨자를 담은 배열을 반환해요.
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
array_position
----------------
2
(1 row)
SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
array_positions
-----------------
{1,4,8}
(1 row)
팁: 배열은 집합(set)이 아니에요. 배열 요소를 찾는 쿼리를 자주 짜고 있다면 데이터베이스 설계를 다시 생각해볼 신호일 수 있어요. 배열 요소가 될 각 항목을 한 행으로 갖는 별도의 테이블을 고려해보세요. 검색이 더 쉬워지고, 요소 수가 많을 때 확장성도 더 좋을 가능성이 커요.
배열 입출력 문법
배열 값의 외부 텍스트 표현은 다음으로 구성돼요. 배열 요소 타입의 I/O 변환 규칙에 따라 해석되는 항목들, 그리고 배열 구조를 나타내는 장식. 그 장식은 배열 값을 감싸는 중괄호({, })와 인접 항목 사이의 구분 문자로 이뤄져요. 구분 문자는 보통 쉼표(,)지만 다를 수도 있어요. 배열 요소 타입의 typdelim 설정에 따라 결정돼요. PostgreSQL 기본 배포판의 표준 타입들은 모두 쉼표를 쓰고, box 타입만 세미콜론(;)을 써요. 다차원 배열에서는 각 차원(행, 평면, 입방체 등)이 자신만의 중괄호 단계를 갖고, 같은 단계의 인접한 중괄호 항목 사이에는 구분 문자가 쓰여야 해요.
배열 출력 루틴은 요소 값이 빈 문자열이거나, 중괄호, 구분 문자, 큰따옴표, 백슬래시, 공백을 포함하거나, 단어 NULL과 일치하면 큰따옴표로 감싸요. 요소 값에 포함된 큰따옴표와 백슬래시는 백슬래시로 이스케이프돼요. 숫자 타입에서는 큰따옴표가 절대 나타나지 않는다고 가정해도 안전하지만, 텍스트 타입에서는 따옴표의 유무 모두에 대비해야 해요.
기본적으로 배열 차원의 하한 인덱스 값은 1로 설정돼요. 다른 하한을 가진 배열을 표현하려면, 배열 내용을 쓰기 전에 배열 첨자 범위를 명시적으로 지정할 수 있어요. 이 장식은 각 배열 차원의 하한·상한을 감싸는 대괄호([])와 그 사이의 콜론(:) 구분 문자로 이뤄지고, 그 뒤에 등호(=)가 따라와요.
SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
e1 | e2
----+----
1 | 6
(1 row)
배열 출력 루틴은 1과 다른 하한이 하나 이상 있을 때만 결과에 명시적 차원을 포함해요.
요소에 NULL(대소문자 무관)을 쓰면 그 요소는 NULL로 취급돼요. 따옴표나 백슬래시가 있으면 이 취급이 꺼지고 리터럴 문자열 "NULL"을 입력할 수 있어요. 또한 8.2 이전 버전과의 호환성을 위해 array_nulls 설정 매개변수를 꺼서 NULL의 NULL 인식을 억제할 수도 있어요.
앞서 보았듯 배열 값을 쓸 때 개별 요소에 큰따옴표를 쓸 수 있어요. 요소 값이 배열 값 파서를 헷갈리게 할 때는 그렇게 해야 해요. 예를 들어 중괄호, 쉼표(또는 타입의 구분 문자), 큰따옴표, 백슬래시, 앞뒤 공백을 포함하는 요소는 큰따옴표로 감싸야 해요. 빈 문자열과 NULL 단어와 일치하는 문자열도 따옴표로 감싸야 해요. 따옴표로 감싼 배열 요소 값에 큰따옴표나 백슬래시를 넣으려면 그 앞에 백슬래시를 붙여요. 아니면 따옴표를 피하고 백슬래시 이스케이프로 배열 문법으로 해석될 모든 데이터 문자를 보호할 수도 있어요.
왼쪽 중괄호 앞이나 오른쪽 중괄호 뒤에 공백을 넣을 수 있고, 개별 항목 문자열 앞뒤에도 공백을 넣을 수 있어요. 이 모든 경우에 공백은 무시돼요. 하지만 큰따옴표로 감싼 요소 안의 공백이나, 요소의 공백이 아닌 문자 양쪽으로 둘러싸인 공백은 무시되지 않아요.
팁: SQL 명령에서 배열 값을 쓸 때는
ARRAY생성자 문법(섹션 4.2.12)이 배열 리터럴 문법보다 다루기 쉬운 경우가 많아요.ARRAY에서는 개별 요소 값을 배열에 속하지 않을 때와 똑같이 쓰면 되거든요.