PostgreSQLのあまり知られていない型3種

postgresに限らずRDBMSでテーブル設計をする上で適切な型を選ぶことは重要です。

postgresはユーザ定義の型を作成できることはご存知かと思いますが、C言語で記述する必要があるため気軽に欲しい型を定義、とは中々いきません。ですが、postgresには既存の型を拡張するような型がいくつか用意されています。これらの型を使うことで柔軟なデータの表現を行うことができるようになります。本記事ではその中から3種類紹介しますので、是非利用してみてください。

1. 配列

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に、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列をunnestすることで簡単に交差表を作ることができることから、これらが意味的に互換であることがわかると思います。

ただし、配列を用いる方法は外部参照の制約を用いることはできなくなります。被参照テーブルが頻繁に更新される場合は整合性を担保することは困難になると思います。データの性質に合わせて適切に選択して下さい。

2. 複合型

複数の型を組み合わせて一つの型とすることができます。c言語でいうstructに相当するものと思えば分かりやすいでしょうか。

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

これテーブル定義と同じでは？と気づいた方もいると思います。実際にテーブルと複合型は非常に近い存在で、テーブルを定義すると暗黙的に対応する複合型も生成されます。気になる方はpg_typeなどのシステムカタログを見てみてください。

リテラル

()で囲って,で区切ると複合型のリテラルになりますが、

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

こちらもROWを用いた行コンストラクタの方が分かりやすいでしょう。

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

値の取り出し

カラム名.要素名で取り出せるのですが、スキーマやテーブルの指定と曖昧になる場合にはエラーになってしまいます。その場合は()で区切ることで回避できます。エラーにならなくてもテーブル名なのか複合型列なのか紛らわしいので、常に()で区切ることをおすすめします。

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

使いどころ

一般的にはテーブルを定義すればそれで事足りるので、複合型は使いどころが難しいのです。使いどころの一つとしては、データを永続化する必要がないが複数の型をまとめて扱いたい場合、例えば関数の戻り値で複数の値を返したい場合などに用いることができます。

3. 列挙型

いわゆるenum型です。

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

列挙型はディスク上では4byte必要なってしまうため、ディスク効率はそれ程よくありません。効率が必要なケースでは、別途マスター情報を持ちつつsmallintなどを用いて整数で表現した方がよいでしょう。

とはいえ、enumを用いることでクエリやデータを分かりやすくできたり、定義されていない値が拒否されることでデータの安全性を上げることが可能です。是非使ってみてください。

ついでに

PostgreSQLのハッカソン的なことをやりたいなと考えているんですが、場所を提供して頂けるところないでしょうか？