database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Tutorials 5233 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 5233
익숙하지 않은 자료형 - Array
작성자
정재익(advance)
작성일
2004-03-02 09:35ⓒ
2004-03-04 14:15ⓜ
조회수
10,029

Array 는 하나의 자료형이라기 보다는 PostgreSQL 에서 자료를 표현하는 하나의 방식이라고 보는 것이 옳다. 다음 Array 에 대한 메뉴얼에 적혀 있는 내용을 번역한 것이다. 처음 PostgreSQL 을 접하는 사람들에게 유용할 듯 싶어서 적어 본다.

8.10. Arrays

PostgreSQL 에서는 table 의 column 을 가변의 길이를 가지는 다차원의 배열(variable-length multidimensional arrays)로서 정의할 수 있다. 어떠한 내장 자료형이나 사용자 정의 자료형에 대해서도 배열을 선언할 수 있다.

8.10.1. Array Types 의 선언

배열 자료형을 선언하기 위해서 다음과 같은 테이블을 생성해 보자:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

위에서 보는 것 처럼, array data type은 배열요소의 자료형의 이름에 square brackets ([]) 을 추가해 준다. 위의 명령은 text (name) 컬럼과, 근로자의 월급을 분기단위로 저장하는, 일차원 배열인 integer (pay_by_quarter) 라는 컬럼, 그리고 근로자의 주간 계획표를 저장할 text (schedule) 라는 컬럼을 가지는 sal_emp 라는 이름의 테이블을 생성하게 된다.

 

CREATE TABLE 문법 내에서 정확한 배열의 크기를 지정해 줄수도 있다. 예를 들면 다음과 같이 한다:

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

그러나. 현재 내부적으로는 배열 크기를 강제적으로 제한하도록 구현되어 있지는 않으며, 이렇게 정의를 하더라도 배열의 크기를 명시하지 않은 경우와 동일하게 동작한다.

실제적으로, 현재의 구현 방식은 차원의 수를 명시하도록 하지도 않는다. 특정 기본 자료형의 배열들은 배열의 크기나 차원에 상관없이, 모두 동일한 자료형으로 간주된다. 그래서 CREATE TABLE  명령어 내에서 배열의 차원이나 크기를 명시하는 것은 단수히 문서상의 표현에 불과하며, 실행시 어떤 영향을 미치지는 않는다.

바꿔말하면, 일차원의 배열에 대해서 SQL99 표준 문법이 사용된다. pay_by_quarter 컬럼은 다음과 같이 정의할 수도 있다.

    pay_by_quarter  integer ARRAY[4],

이 문법에서는  배열의 크기를 명시하기 위해서 정수 상수를 표시하였다. 그러나 이렇게 크기를 명시했다고 해서 PostgreSQL 은 배열의 크기에 제한을 두지는 않는다.

8.10.2. 배열값의 입력

배열값에 문자상수를 입력하고자 한다면, 각 배열요소의 값을 curled braces({ })로 감싸 주고, 각각의 값들을 comma (,)로서 분리해 준다. (만약 여러분들이 C 언어를 안다면, 이것은 C 에서 구조체를 초기화하는 문법과 동일하다). 어떠한 배열요소의 값이라도 double quotes 로 감쌀수 있는데, 만약 배열요소값의 내부에 comma(,)나 curly braces({ })를 포함하는 경우에는 더더구나 그러하다. (보다 상세한 내용은 아래에 적도록 하겠다). 그리하여 배열상수의 일반적인 형식은 다음과 같다:

'{ val1 delim val2 delim ... }'

여기서, delim 는 자료형의 형구분문자(type delimeter character)이고, 그것의 pg_type entry에 기록되어 있다. (모든 내장 자료형에 대해서 (built-in types), 이 문자는 comma","로 정의되어 있다.) 각각의 val 는 배열요소 자료형의 상수이던지 또는 하위배열 (subarray)이어야 한다. 다음은 배열 상수의 예이다.

'{{1,2,3},{4,5,6},{7,8,9}}'

상수는 2차원이고, 정수형인 3개의 subarray 를 가지는 3-by-3 array 이다.

(이들 배열상수의 종류는 Section 4.1.2.4. 에 기술된 일반적인 형의 상수의 특수한 경우에 해당한다. 상수는 초기에 문자열로서 다뤄지고, 배열입력변환 루틴 (array input conversion routine)으로 전달된다. 명시적으로 자료형을 표시해줄 필요성이 있다)

이제 INSERT 구문을 살펴보도록 하자.

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"talk", "consult"}, {"meeting"}}');

현재 배열 구현의 제한은 각각의 배열 요소들은 SQL null value 를 가질수 없다는 것이다. 전체 배열 값이 null 이 될수는 있으나, 배열의 일부 요소가 null 이 될수는 없다.

이것은 놀랄만한 결과를 유도해 낸다. 예를 들면, 위의 두 INSERT 구문의 결과는 다음과 같이 보일 것이다:

SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |      schedule
-------+---------------------------+--------------------
 Bill  | {10000,10000,10000,10000} | {{meeting},{""}}
 Carol | {20000,25000,25000,25000} | {{talk},{meeting}}
(2 rows)

schedule 컬럼의 [2][2] 요소가 INSERT 구문에서 빠져 있기 때문에, [1][2] 요소는 버러지게 된다.

Note: 이것은 차기버전에서 수정될 것이다.

ARRAY 표현 구문은 다음과 같이 사용되어진다:

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['','']]);

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['talk', 'consult'], ['meeting', '']]);
SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |           schedule
-------+---------------------------+-------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{"",""}}
 Carol | {20000,25000,25000,25000} | {{talk,consult},{meeting,""}}
(2 rows)

위의 구문에서 주의해서 볼점은, 다차원의 배열은 각각의 차원의 크기에 맞추어져야 한다는 것이다. 만약 이것이 맞추어지지 않을 경우 이전처럼 아무런 경고없이 값을 버리기 보다는, 에러를 발생시킨다. 예를 들면 :

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['talk', 'consult'], ['meeting']]);
ERROR:  multidimensional arrays must have array expressions with matching dimensions

배열요소들은 일반적인 SQL 상수이거나 또는 표현식이라는 것에 주의해야 한다; 예를 들면, 문자열은 배열 문자에 사용하는 것 처럼 double quotes ("문자열"의 형식)를 사용하는대신 single quotes('문자열'의형식) 로 둘러싼다.

ARRAY  구문에 대해서는  Section 4.2.10 에서 보다 상세하게 언급하도록 하겠다.

8.10.3. 배열 사용하기

이제, 우리는 테이블에상에서 배열에 대해서 질의를 실행해 볼수 있다. 먼저 배열의 요소중 한개를 추출하는 질의를 실행해 보자. 다음은 제이사분기에 월급이 변한 근로자의 이름을 찾는 질의이다:

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

배열 첨자 인수는 사각 괄호 내에 적힌다. PostgreSQL 은 배열 인자를 표시할때 인자 번호가 1 부터 시작한다. 즉  n 개의 요소를 가지는 배열은 array[1] 에서 시작하여 array[n]으로 끝나게 된다. 

다음 질의는 모든 근로자의 제삼사분기 월급을 보는 질의이다:

SELECT pay_by_quarter[3] FROM sal_emp;

 pay_by_quarter
----------------
          10000
          25000
(2 rows)

전체 배열 중에 임의의 부분 (rectangular slices), 또는 subarray 를 추출해서 볼수도 있다. Array slice 는 하부경계점:상부경계점(lower-bound:upper-bound)을 명시하여 하나이상의 배열 차원을 표현한다. 예를 들면, 다음 질의는 Bill의 schedule 중에서 각주의 처음 이틀간의 스케쥴 중에서 첫번째 item 만을 추출하는 것이다.

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

      schedule
--------------------
 {{meeting},{""}}
(1 row)

물론 다음과 같이 적을 수도 있다.

SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';

같은 결과를 보여준다. 배열 인자 연산은 lower:upper 형식으로 적힐 경우 항상 array slice 로 표현된다. 다음 예에서 처럼 하부경계(lower bound)가 1일 경우 하나의 값이 명시된 인자로서 간주된다:

SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
         schedule
---------------------------
 {{meeting,lunch},{"",""}}
(1 row)

어떤 배열값의 현재 차원수는 array_dims 함수를 이용하여 알수 있다:

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

 array_dims
------------
 [1:2][1:1]
(1 row)

array_dims 함수는 text 로서 결과값을 돌려준다. 이것은 사람들에게는 읽기 편하겠지만 프로그래밍 하기에는 편리한 방식이 아니다. 차원수는 array_upperarray_lower 를 이용해서 알수도 있는데, 이들은 각각 특정 배열 차원의 상한경계와 하한경계값을 돌려준다.

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_upper
-------------
           2
(1 row)

8.10.4. 배열변경하기

배열값은 다른 값으로 완전히 치환될 수도 있다:

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';

또는 slice 단위로 갱신할 수도 있다:

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

이미 저장되어 있는 배열 값들은 이웃한 기존의 배열요소의 값을 할당하여 배열요소의 수를 더 많게 할수도 있다. 또는 이웃한 slice 를 할당하던지 또는 이미 존재하는 값에 겹쳐 쓸수도 있다.  예를들면, 배열 myarray 가 4 개의 요소를 가진다고 가정하자. 그리고 갱신된 후에는 5개의 인자값을 가지게 되고 그 값은 myarray[5]에 할당될 것이다. 현재, 이와 같이 배열요소를 확인하는 것은 일차원 배열에 한해서만 허용이 되며, 다차원 배열의 경우는 허용이 되질 않는다.

Slice 단위로 배열 할당을 하는 것은 one-based subscripts (배열인자가 처음 값이 1로 시작하는것) 사용하지 않는 배열을 생성할 수 있도록 해 준다. 예를 들면, myarray[-2:7] 라는 배열에 값을 할당하여, 배열인자 값이 -2 에서 7이 되도록 할수도 있다.

새로운 배열 값은 concatenation operator(연결연산자), || 를 이용하여 생성할수도 있다. 

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)

이 연결 연산자는 (concatenation operator) 한 배열 요소를 일차원 배열의 처음이나 끝부분에 넣을수 있도록 해준다. 이것은 또한 두개의 N-차원 배열이나, N-차원 배열과 N+1-차원 배열도 허용한다.

단일 배열 요소가 일차원 배열의 시작부분에 들어갈 경우, 결과는 배열첨자 (배열인자)의 하한경계 값은 기존의 하한경계값-1 이 된다. (the result is an array with a lower bound subscript equal to the right-hand operand's lower bound subscript, minus one.) 하나의 배열 요소가 일차원 배열의 끝부분에 추가될 경우 상한경계값이 기존 값에 1 이 더해지게 된다.

예를 들면:

 

SELECT array_dims(1 || ARRAY[2,3]);
array_dims
------------
[0:2]
(1 row)

SELECT array_dims(ARRAY[1,2] || 3);
array_dims
------------
[1:3]
(1 row)

 

같은 차원의 두개의 배열이 연결될 경우, 결과는 lower bound subscript (하한경계 인자값)는 좌측 array 의 값에 우측  array 의 인자수만큼 더해진 값이 할당 된다. 배열의 순서는 좌측의 배열 요소 다음에 우측 배열의 요수가 할당되게 된다. 예를 들면:

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-차원 sub-array 가 근본적으로 N+1-차원 배열의 요소가 된다. 예를 들면:

SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
 array_dims
------------
 [0:2][1:2]
(1 row)

배열은 array_prepend, array_append, 또는 array_cat 함수를 이용하여 재구성할 수도 있다. 처음 두개는 일차원 배열에서만 이용가능하다. 그러나 array_cat 은 다차원 배열을 지원한다. 위에서 언급한 연결연산자가 이들 함수에서 직접적으로 이용된다는 사실을 명심하기 바란다. 사실, 이 함수가 연결연산자를 구현하기 위해서 이용된 함수이다. 그러나 이들은 사용자 정의 집합함수 (aggretages) 를 구현하는데 유용하게 쓰일 수 있다. 예를 들어 보자:

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}}

8.10.5. 배열 내 검색

배열 내의 어떤 값을 찾고자 한다면, 여러분들은 각각의 배열 값들을 검사해야만 한다. 만약 여러분들이 배열의 크기를 알고 있다면 이것은 수작업으로 할수 있다. 예를 들어보자 :

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;

그러나, 이것은 배열의 크기가 클 경우 상당히 느려지게 되며, 더군다나 배열의 크기를 알수없는 경우에는 더더구나 도움이 되질 않게 된다. 다른 방법이 Section 9.17 에 기술되어 있다. 위의 query 는 다음으로 대체할 수 있다:

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

게다가, 여러분들은 배열요소값이 10000 인 모든 배열들을 찾을수도 있다:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

Tip: 배열은 집합이 아니다; 특정 배열요소를 찾아야 하는 상황이 발생한다면 이것은 데이터베이스 디자인이 잘못되었다는 신호일수 있다. 각각의 item 이 배열의 요소가 되는 row 를 가진 분리된 테이블 고려해 보도록 하라. 이것이 검색을 더욱더 용이하게 할 것이다. 그리고 보다 많은 수의 요소를 쉽게 검색할 수 있도록 해 줄것이다.

8.10.6. 배열 입력과 출력 구문

 

배열 값의 외부적인 텍스트 표현 (external text representation)은 배열 요소의 자료형을 위한 I/O conversion rule 에 따라 해석되는 item 들과 배열의 구조를 가르키는 문자열로 (일종의 장식문자열) 구성된다. 이러한 장식 문자열은 curly braces ({ and }) 로 둘러싸인 배열 값과 이웃한 item 간을 나누어 주는 구분자로 구성되어 있다. 구분문자는 일반적으로 comma (,) 가 사용되지만, 다른 것이 될수도 있다: 이것은 배열 자료형의 typdelim 설정에 의해 결정되어 진다. (PostgreSQL 에 의해 제공되는 표준 자료형들 중에, box 자료형은 semicolon (;) 을 사용한다. 그러나 다른 모든 자료형들은 comman 를 사용한다) 다차원 배열에서 각각의 차원들 (row, plane, cube, etc.)은 각자의 레벨에서 각각 curly brace를 가진다. 그리고 구분자는 각각의 레벨에서 이웃한 curly-braced entities 사이에 존재하게 된다. 좌측 brace 앞과 각각의 item 문자열 앞에는 white space 를 위치시켜야 한다. 그러나 item 뒤에 있는 whitespace 는 무시되지 않는다. 앞에 오는 whitespace 는 건너 뛴 후에 우측 brace 나 delimiter꺼지는 item value 로서 받아 들여지게 된다.

 

앞에서 본것 처럼, 여러분들이 각각의 배열 요소의 값을 적을때  각각의 배열요소 값을 double quote 로 둘러 싸야 한다. 여러분들은 배열요소 값들이 array-value parser 가 혼돈할 염려가 있을때에는 특히 그렇게 해야 한다. 예를 들면 배열요소가 curly braces, commas (또는 어떤 다른 구분문자 (delimiter character)), double quotes, backslashes, 또는 선행하는 공백문자가 있는 경우 특히 double-quote 로서 감싸줘야 한다. quote 로 둘러싸인 배열 요소 값내에 double quote 나 backslash 를 가지는 경우, 그 앞에 backslash (\) 를 넣어 줌으로서 문자를 표현해 준다. (escape 시켜 준다).  다른 방법으로, 여러분들은 배열 문법이나, 또는 공백 문자가 무시 되지 않도록 하기 위해서 backslash-escaping 을 시켜 줄수 있다.

배열 출력 루틴은 배열 요소 값이 빈문자열이던지, 또는 배열 문자열 속에 curly braces, delimiter characters, double quotes, backslashes, 또는 white space 가 포함되는 경우 double quotes 를 넣어 줘야 한다. 배열 요소 값속에 double quote (") 와 backslash (\) 가 포함되어 있는 경우 backslash-escaping 을 시켜 줘야 한다.

수치 자료형의 경우, double quote 를 사용하지 않는 것이 더 안전하다. 그러나 텍스트형의 자료의 경우, quote 가 있던지 없던지 간에 사용하는 것이 좋다. (이 것은 pre-7.2 PostgreSQL releases 부터 변경되었다.)

Note: SQL 명령어에서 쓰여진 문자는 먼저 literal (문자열)로서 처리되고 그 다음에 배열로서 해석되어진다. 이것은 backslash 의 수를 여러분들이 원하는 것보다도 두배로 적어줘야 함을 의미한다. 예를 들면, backslash 와 double quote 를 포함하는 text array value 를 insert 하고자 한다면 다음과 같이 적어 줘야 한다.

INSERT ... VALUES ('{"\\\\","\\""}');

string-literal processor 가 일차적인 레벨의 backslashes를 제거하고, array-value parser 에 도달했을 때에는 {"\\","\""}와 같은 모습으로 보여지게 된다.  바꾸어 말하면, 문자열은 text data type 입력 루틴에서 각각 \"  로 변경되게 된다. (만약 backslash 도 특별하게 다루는 자료형의 입력루틴에서 작업을 한다면, 예를 들어 bytea 와 같이, 배열요소로서 하나의 backslash 를 저장하기 위해서 8개의 backslash가 필요할 것이다)

Tip: ARRAY 생성자 문법은 SQL 명령어내에서 배열 값을 쓸대 array-literal syntax 보다도 용이할 경우가 많다. ARRAY 명령어를 이용하면, 개별적인 요소값들은 배열의 인자가 아닐때와 동일하게 값을 적을수 있다.

이 글에 대한 댓글이 총 4건 있습니다.

pgsql에 배열자료형이 없었다면.. 아마 외면했을지도 -.-;

잦은 검색이 일어나지 않고 언제든지 확장될 수 있는 데이터를 저장하기에는 배열자료형이 최고인것 같습니다..(이 용도가 아니죠 사실은? ㅠ_ㅠ;)

저는 opt 라는 text[] 컬럼을 만들어서 여기에 다 때려박아줍니다 -.-;

의외로 편하더라구요.. -_-;;; 새로운 변수가 추가되도 테이블 변경할 필요 없이 배열 원소 하나 추가..

int형이라면 gist 인덱스도 되고.. 여튼 강추입니다~

신기배(nonun)님이 2004-03-02 10:15에 작성한 댓글입니다.

헉!

작업중 리플이네요.

오늘 내로 완성할 생각입니다. 그런데 요즘 장사가 안되니 이짓이나 하고 있구... 내 동료들이 날 보면 뭐라 할련지~~

정재익(advance)님이 2004-03-02 10:27에 작성한 댓글입니다.

버전에 상관없이 다 지원 되는 겁니까?

강성일님이 2004-03-05 17:00에 작성한 댓글입니다. Edit

array 자료형 자체는 버전에 크게 상관없이 지원됩니다. 초창기부터 지원되던 feature 이니까요. 하지만 array 용 연산자는 하위 버전에서는 지원되지 않는 경우가 있습니다.

 

현재 이글은 최신 버전 기준입니다. 7.4.2 이던가??

정재익(advance)님이 2004-03-31 12:58에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
5383DB 인코딩 컨버전 (서버 <-> 클라이언트) [5]
신기배
2004-06-25
10064
5263익숙하지 않은 자료형 - oid (Object identifer type)
정재익
2004-03-31
9843
5245익숙하지 않은 자료형 -bytea (binary data types) [1]
정재익
2004-03-10
9603
5233익숙하지 않은 자료형 - Array [4]
정재익
2004-03-02
10029
5232로또로 배우는 인덱스 :) [2]
김상기
2004-02-27
7160
5230Pseudo-Types in PostgreSQL
정재익
2004-02-27
6454
5192재미난 문자열 집계 함수 [8]
김상기
2004-02-05
10040
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.051초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다