PostgreSQLで配列型のカラムを使ってみる

PostgreSQLではカラムのデータ型に配列を使うことが出来る。いちおうSQL99準拠なのかな。

何はともあれテーブルを作成する

SQLはこんな感じ。

CREATE TABLE favorites (
    username varchar(20),     -- ユーザ名
    category varchar(20),     -- カテゴリ名
    items    varchar(100)[],  -- アイテム名（複数）
    PRIMARY KEY(username, category)
);

itemsの部分が配列。プログラミング言語のように型名に括弧を付けた形で指定する。
括弧内に要素数を指定することもできるが、現在の実装では要素数の指定は無視され、無制限として扱われるとのこと（但しSQL99の仕様では要素数は固定にしかできない）。
また、配列の宣言方法はSQL99に準拠したitems varchar(100) ARRAY[10]のような書き方も出来る。この場合要素数の指定は必須となるが、現在の実装ではやはり何を指定しても無制限として扱われるとのこと。

items varchar(100)[][]のように書けば多次元配列も可能。

データを入れてみる

テーブルが作成できたので早速データを投入してみる。

INSERT INTO favorites (username, category, items)
    VALUES ('ぱせらん', '果物', '{オレンジ, メロン, りんご}');

プログラミング言語の配列や構造体のような形で指定する。但し全体をシングルクォートで括ってあげる必要がある。カンマや中括弧などが含まれる場合はさらに個々の要素をダブルクォートで括る必要がある。
こちらも次のようにSQL99準拠な書き方もできる。こちらのほうが個々の要素をクォートで括れるので、感覚的にはわかりやすいかもしれない。

INSERT INTO favorites (username, category, items)
    VALUES ('ぱせらん', '色', ARRAY['Black', 'Green', 'Blue']);

データを抽出してみる

データも作成できたので、今度はそれを取り出してみる。

SELECT username, category, items FROM favorites;

 username | category |          items

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

ぱせらん | 果物 | {オレンジ,メロン,りんご} ぱせらん | 色 | {Black,Green,Blue} (2 rows)

このような感じで出てくる。
特定のインデックスだけ取得する場合は添字を指定する。添字は1から始まる。

SELECT username, category, items[1] FROM favorites;

 username | category |  items

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

ぱせらん | 果物 | オレンジ ぱせらん | 色 | Black (2 rows)

添字部分にlower:upperという指定をすることで特定の範囲を抽出することも出来る。
次の例は2番目と3番目の要素からなる副配列として抽出する。

SELECT username, category, items[2:3] FROM favorites;

 username | category |      items

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

ぱせらん | 果物 | {メロン,りんご} ぱせらん | 色 | {Green,Blue} (2 rows)

範囲外やNULLを添字に指定した場合はエラーにはならずにNULLが返る。

SELECT username, category, items[NULL] FROM favorites;

SELECT username, category, items[20] FROM favorites;

 username | category | items

• • • • • • • • • • • • • • • • • • • • • • • • • • • • -

ぱせらん | 果物 | ぱせらん | 色 | (2 rows)

検索してみる

WHERE句を指定して検索してみる。

SELECT username, category, items FROM favorites
    WHERE items[1] = 'オレンジ';

 username | category |          items

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

ぱせらん | 果物 | {オレンジ,メロン,りんご} (1 row)

配列の長さがわかっている時はitems[1] = 'A' OR items[2] = 'A' ...というように書くことも出来るが、長さが不明な場合や非常に長い場合には困るのでANY構文を使う（SOMEでもよい）。
同様に全ての値に対して評価を行うALLもある（例は割愛）。

SELECT username, category, items FROM favorites
    WHERE 'オレンジ' = ANY (items);

 username | category |          items

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

ぱせらん | 果物 | {オレンジ,メロン,りんご} (1 row)

パターンマッチも同様に行いたいのだが、何故か上手くいかなかった（2007年3月1日現在）。

SELECT username, category, items FROM favorites
    WHERE 'オ%' LIKE ANY (items);

[期待する結果]
 username | category |          items

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

ぱせらん | 果物 | {オレンジ,メロン,りんご} (1 row) [実際の結果] username | category | items

• • • • • • • • • • • • • • • • • • • • • • • • • • • • -

(0 rows)

任意の条件に合致する要素を取得する方法は見つからなかった（例えばメロンが何番目の要素なのか、など）。
ストアドプロシージャなどを駆使すれば出来るのかもしれないが、一発で出来るような方法はないようだ。

更新してみる

配列のデータを更新してみる。

UPDATE favorites SET items = '{オレンジ, パイナップル, りんご}'
    WHERE username = 'ぱせらん' AND category = '果物';

[更新後]
 username | category |             items

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

ぱせらん | 色 | {Black,Green,Blue} ぱせらん | 果物 | {オレンジ,パイナップル,りんご} (2 rows)

上の例はitemsを丸ごと変更する例。INSERTの時と同様にARRAY構文を使うことも出来る（例は省略）。
次のように配列の特定の要素だけ更新することも出来る（「りんご」だけ平仮名だったので片仮名にしてみる）。

UPDATE favorites SET items[3] = 'リンゴ'
    WHERE username = 'ぱせらん' AND category = '果物';

[更新後]
 username | category |             items

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

ぱせらん | 色 | {Black,Green,Blue} ぱせらん | 果物 | {オレンジ,パイナップル,リンゴ} (2 rows)

任意の条件に合致する要素が取得できそうにないので、「配列中の『りんご』を『リンゴ』にする」といったSQLは書けないようだ。

ホスト言語からの利用

配列をホスト言語で扱った場合に果たしてどうなるのか。
自分ではまだ試していないのだが、ざっと調べたところによるとPHPでは配列には対応していないらしい。
残念ながら{オレンジ,パイナップル,リンゴ}という文字列で返ってくるので、自分で分解する必要があるようだ。

JavaはJDBCドライバの実装によるのかもしれないが、java.sql.Array型として扱うことができるようだ。

Perl……未調査。

Ruby……未調査。

結局配列はどうなのか

基本的には配列的なデータ構造になった場合は正規化して別テーブルに括り出すことになるはずなので、何か特別な理由でもない限りは配列型を利用する必要はないと思われる。
実際PostgreSQLのマニュアルにも次のように書かれている。

ティップ: 配列は集合ではありません。特定の配列要素に検索をかけることはデータベース設計が誤っている可能性を示唆しています。配列の要素とみなされるそれぞれの項目を行に持つ別のテーブルを使うことを検討してください。この方が検索がより簡単になり要素数が大きくなっても拡張性があります。

ホスト言語から見ても扱いにくいことこの上なさそうなので、出来れば使わないほうがよさそうだ。

ちなみに、使わないほうがよいと思われる配列型がわざわざ出てきた理由については、新しい業界標準「SQL99」詳細解説（ITエキスパートのための問題解決メディア - ＠IT）で次のように書かれている。

列の中に配列を許すということは、リレーショナルモデルの考え方からすれば、第一正規形に違反した非正規形なのでとても許されないことのように思えるが、ユーザーや市場の強い要求によって実現した。実際、SQL99の関連規格であるSQL/MMは、フルテキストや空間データ情報を表現するのに配列型を多用しているので、一層標準化が急がれた。

自分にはその辺の知識がないのでどのような実態や要望があるのかはわからないが、空間データというあたりから{x座標,y座標,z座標}のようひとまとまりのデータを扱うのに都合がよいのかもしれない。
もっともPostgreSQLには幾何データ用のデータ型が別途用意されているし、ユーザ定義の複合型を定義することもできるので、配列でそれらを表現する必要はなさそうだ。

■参考
http://www.postgresql.jp/document/pg823doc/html/arrays.html（日本PostgreSQLユーザ会 | 日本PostgreSQLユーザ会）
http://www.akimoto-jp.com/java/Database/jdbcSQL99.html（http://www.akimoto-jp.com/java/index.html）
新しい業界標準「SQL99」詳細解説（ITエキスパートのための問題解決メディア - ＠IT）

続きもどうぞ：PostgreSQLで複合型のカラムを使ってみる - ぱせらんメモ