[PostgresSQL] PostgresSQL에서 잘 알려지지 않은 형태 세 가지

※ 일본의 한 블로그 글을 번역한 포스트입니다. 오역 및 의역, 직역 있을 수 있으며 틀린 내용은 지적해주시면 감사하겠씁니다.

 

 postgres에 한정하지 않고 RDBMS에서는 테이블 설계를 할 때 적절한 데이터형을 고르는 것이 중요하다. 또한 postgres에서는 기존의 데이터형을 확장한 형태가 몇 가지 준비되어 있다.

 이러한 데이터형을 사용하는 것으로 유연한 데이터의 표현을 할 수 있다. 이번 포스팅에서는 그 중에서 세 종류를 소개하고자 한다.

 

 

배열

---

 postgres에서는 임의의 데이터 형의 배열형을 사용할 수 있다.

 

데이터형의 정의

배열로 하고 싶은 데이터형의 뒤에 []를 추가한다. 예를 들면 다음과 같다.

int[]

 

리터럴

{}를 감싸고 ,로 구분된 배열의 리터럴 표현은 다음과 같이 할 수 있다.

SELECT '{1,2,3}'::int[]

그러나 ARRAY를 이용한 배열 컨스트럭터쪽이 더욱 알기 쉽기 때문에 이렇게 작성하는 것을 추천한다.

SELECT ARRAY[1,2,3]::int[]

 

행 > 배열의 변환

집약 함수인 array_agg를 사용하여 지정한 행을 집약해서 배열로 만들 수 있다. 

$ SELECT i FROM foo;

 i
---
 1
 2
 3
(3 rows)

$ SELECT array_agg(i) FROM foo;

 array_agg
-----------
 {1,2,3}
(1 row)

 

배열 > 행의 변환

 반대로 배열의 각 요소를 분리하고 싶은 경우 unnest를 사용한다.

$ SELECT * FROM unnest(ARRAY[1,2,3]);

 unnest
--------
      1
      2
      3
(3 rows)

 

연산자와 함수

 배열을 조작하는 몇 가지 연산자와 함수가 준비되어 있지만, 기본적으로는 위에서 언급한 unnest와 array_agg와 함께 일반적인 SQL로 조작하는 편이 보기도 알기도 쉬우므로 추천한다.

 자주 사용되는 조작에 대해서는 SQL 함수를 정의하여 이름을 명명해놓는 것도 좋을 것이다. 예를 드어, 정수의 배열을 정렬하여 중복된 요소를 삭제하는 조작은 아래와 같이 만들 수 있다.

CREATE FUNCTION int_array_sort_uniq(int[]) RETURNS int[] AS $$
    SELECT
        array_agg(DISTINCT i ORDER BY i)
    FROM
        unnest($1) AS t(i)
$$ LANGUAGE SQL;

$ SELECT int_array_sort_uniq(ARRAY[2,4,3,2,1]);
 int_array_uniq_sort
---------------------
 {1,2,3,4}
(1 row)

 조합으로서 비교적 자주 사용되는 것은 다음과 같다.

@> 포함을 표현하는 연산자.

SELECT ARRAY[1,2,3] @> ARRAY[2]; -- true

|| 배열이나 값을 결합

SELECT ARRAY[1,2,3] || ARRAY[4,5,6]; -- {1,2,3,4,5,6}

array_length 배열의 길이

SELECT array_length(ARRAY[1,2,3],1); -- 3

 

ANY/ALL

 배열의 각 요소에 논리값을 반환하는 연산자를 적용할 수 있다. ANY의 경우 어떠한 하나의 요소가 true면 true로, ALL의 경우 모든 요소가 true인 경우에만 true를 반환한다.

SELECT 1 = ANY(ARRAY[1,2,3]); -- true
SELECT 1 = ALL(ARRAY[1,1,1]); -- true

 

인덱스

 GIN 인덱스를 이용하여 배열의 각 요소에 인덱스를 부여할 수 있는 것이 가능하다. 예를 들어, 어떤 배열에 있는 값을 가지고 있는지이라는 검색하는 것에 대해 인덱스를 적용하는 것도 가능하다.

$ CREATE TABLE foo(ints int[]);
$ CREATE INDEX idx_foo_ints ON foo USING gin(ints);
$ INSERT INTO foo VALUES(ARRAY[1,2,3]), (ARRAY[4,5,6]);
$ EXPLAIN SELECT * FROM foo WHERE ints @> ARRAY[1];
                                 QUERY PLAN
----------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=12.05..22.20 rows=7 width=32)
   Recheck Cond: (ints @> '{1}'::integer[])
   ->  Bitmap Index Scan on idx_foo_ints  (cost=0.00..12.05 rows=7 width=0)
         Index Cond: (ints @> '{1}'::integer[])
(4 rows)

 

사용되는 경우

 배열형은 매우 강력하나, 남용하면 테이블의 정규형을 무너뜨리게 된다. 배열을 이용할 때에는 기본적으로 테이블의 하나의 예로 정의해도 모순이 없는가를 생각해 볼 필요가 있다.

 실제 조작 상으로는 unnest/array_agg로 상호 변환이 가능하지만, 데이터의 의미적으로 상호환된다. 그러므로, 쿼리의 최적화나 표현의 쉬움에 관련된 시점에서는 배열형으로 정리할 것인지, 다른 테이블로 분리할 것인지를 선택한다.

 대표적으로는 m:n의 관계 표현일 것 이다. 일반적으로 m:n의 관계에 있는 테이블은 교차표를 사용하여 표현한다. 배열형을 사용하는 것으로 교차표를 사용하지 않고 직접 m:n의 관계를 표현할 수 있다.

 예를 들어 블로그 하나의 포스트에 여러 개의 태그가 연결된 경우, 교차표를 사용한다면 다음과 같이 표현된다.

$ CREATE TABLE post(post_id serial, title text, body text);
$ CREATE TABLE post_tag(id serial, post_id int, tag_id int);
$ CREATE TABLE tag(tag_id serial, name text);

 

 한편으로 배열을 이용한다면 다음과 같다.

CREATE TABLE posts(post_id serial, title text, body text, tag_ids int[]);
CREATE TABLE tags(tag_id serial, name text);

 배열을 사용한 경우에도 tag_ids열을 innest하는 것으로 간단히 교차표를 만드는 것이 가능하므로, 이것이 의미적인 호환이라고 할 수 있다.

 그러나 배열을 이용하는 방법은 외부 참조 제약을 이용하는 경우 할 수 없게 된다. 피참조 테이블이 빈번히 갱신되는 경우는 정합성을 담보하는 것이 어렵기 때문이다. 데이터의 성질에 맞춰서 적절히 선택하길 바란다.

 

 

복합형

---

 여러 개의 형태를 합쳐서 하나의 형태로 만드는 것도 가능하다. C언어로 말하자면 struct에 상당하는 것이라고 생각하면 알기 쉬울 것이다.

CREATE TYPE item AS (
    name text,
    price int
);

 테이블 정의와 비슷한 거 아닌가라는 생각할 수 있다. 실제로 테이블과 복합형은 매우 가까운 존재로 테이블을 정의하면 암묵적으로 대응하는 복합형이 생성된다. 

 

리터럴

()로 감싸고 , 로 부분하면 복합형의 리터럴이 된다.

SELECT '(''orange'', 100)'::item;

 이 경우 ROW를 이용한 행 컨스트럭터가 알기 쉬우므로, 아래의 방법을 추천한다.

SELECT ROW('orange',100)::item;

 

값의 추출

 칼럼명.요소명으로 추출하는 것이 가능하지만, 스키마나 테이블의 지정이 애매한 경우에는 에러가 된다. 이 경우는 ()로 구분짓는 것으로 이러한 에러를 피할 수 있다. 에러가 되지 않아도 테이블명인지 복합형의 열인지 헷갈리기 쉬우므로 항상 ()로 구분해주는 것을 추천한다.

 

사용되는 경우

 일반적으로는 테이블을 정의하면 그것으로 충분하므로, 복합형은 사용되는 경우가 드물다. 그러나 하나 예를 들자면, 데이터를 영속화할 필요가 없지만 여러개의 형태로 엮어서 다루고 싶은 경우, 함수의 반환값으로 여러 개의 값을 반환하고 싶은 경우 등에 사용할 수 있다.

 

 

열거형

---

말하자면, enum형이다.

CREATE TYPE color AS ENUM(
    'red',
    'green',
    'blue'
);

 열거형은 디스크상에서는 4byte가 필요하게 되므로 디스크 효율을 그닥 좋지 않다. 효율성을 확보해야할 필요가 있는 경우 다른 마스터 정보를 가지면서 smallint등을 이용하여 정수로 표현하는 경우가 좋다.

 한편으로, enum을 이용하는 것으로 쿼리나 데이터를 알기 쉽게 하거나, 정의되지 않은 값은 부정되는 것으로 데이터의 안전성을 올리는 것이 가능하다.

---

참고자료

https://qiita.com/choplin/items/9d5e2ff8721fb9509bf8