他のバージョンの文書9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

5.12. 配列

PostgreSQL はテーブルの列を可変長多次元の配列として定義することができます。また、既存のデータ型やユーザー定義型の配列を作成することも可能です。実際の配列の使いかたを説明するために、つぎのテーブルを作成します。

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

見てお解りのように配列データ型は配列要素のデータ型の名前に鈎カッコ([])をつけて指定します。 上記のコマンドは text型文字列 (name)、従業員の四半期の給与を保存する integer 型の一次元配列 (pay_by_quarter)、そして従業員の週間スケジュールを保存する text 型の二次元配列 (schedule) の列を持つ sal_emp という名前のテーブルを作成します。

それでは INSERT をいくつか実行してみましょう。 配列に要素の値を追加する際にはその要素の値を中カッコで囲み、それぞれの要素の値をカンマで区切っていることに注意してください。 C 言語を知っているならば、構造体を初期化するための構文のようなものと考えてください。

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"}}');

それでは、sal_emp に対して問い合わせを行ってみましょう。まず、一回にひとつの配列の要素にアクセスするしかたを示します。この問い合わせは第2四半期に給料の変更があった従業員の名前を抽出します。

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] で終わります。

この問い合わせはすべての従業員の第3四半期の給与を抽出します。

SELECT pay_by_quarter[3] FROM sal_emp;

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

また、配列や副配列の任意の一部分を取りだすことも可能です。 1 次元以上の配列について配列の一部を書くには、lower-bound:upper-bound と記述します。 この問い合わせは Bill のその週の初めの 2 日 に最初なにが予定されているかを抽出します。

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 という形式で書かれていると、配列の一部を表していると想定します。 1 つの値だけが指定される場合、任意の添字について下限を 1 と仮定します。

配列の値をすべて置き換えることも可能です。

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

または 1 つの要素を更新することも可能です。

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

配列は、すでに存在している配列要素の直後、もしくはデータがすでに入っている配列の一部分に上書き/挿入することによって配列の大きさを変更できます。たとえば、配列の要素が現在 4 つあるとし、array[5] を指定するとその配列の要素は 5 つになります。現在では、このような配列の拡張は 1 次元配列でのみ可能となっていて、多次元配列ではできません。

配列の一部の指定で 1 始まり以外の添字がある配列を作れます。例えば添字が -2 から 7 までの値をもつ配列を array[-2:7] で指定できます。

CREATE TABLE の構文で固定長の配列を定義できます。

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

とはいっても現在の実装では、配列の最大サイズを強要しません。 大きさを指定していない配列と同じ動作になります。

実際には、現在の実装では次元の宣言も強要しません。配列の大きさや次元数にかかわらず、ある特定の基本型の配列はすべて同じ型であるとみなされます。ですから次元とか大きさの数を CREATE TABLE 宣言したとしてもそれは単なる記述にすぎず、実行時の振舞に影響を与えません。

array_dims 関数で任意の配列値の現在の次元を取り出せます。

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

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

array_dims 関数は、text 型で結果を返します。 人間が結果を見るためには便利ですが、プログラムにとってはあまり有効ではないかもしれません

配列内である値を検索するには、配列の要素すべてを検索する必要があります。もし配列の大きさがわかっているならば手作業でも検索できます。

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;

とはいってもこの方法では大きい配列では大変な作業となりますし、配列の大きさが不明な場合この方法を実行できません。PostgreSQL の標準配布物に入っていないとはいっても、配列値に対して繰り返し処理を行う新しい関数や演算子を定義する拡張が使えます。これらを使用して、さきほどの問い合わせを以下のように書くことができます。

SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000;

配列の(特定の列のみだけではなく)すべての要素を検索するにはつぎのようにします。

SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000;

さらに値が 10,000 と一致する配列の行も下記のようして検索できます。

SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000;

このオプションモジュールをインストールするには PostgreSQL のソースディストリビューションの contrib/array ディレクトリを参照してください。

Tip: 配列は集合ではありません。 前項の例のように配列を使用しなければならないときは、えてしてデータベースの設計ミスである可能性が大きいということです。配列のフィールドは一般的にはそれぞれ違うテーブルであるべきです。テーブルは明らかに検索するのに便利です。

Note: 現時点での配列実装の限界は個々の配列の要素が SQL の null 値であってはならないことです。 配列全体を null に設定することはできますが、配列の要素に null と null でないものを混在させることはできません。この問題の解決は TO-DO リストに挙げてあります。

配列入出力構文. 配列の値の外部表現は配列の要素の型に対する I/O 変換ルールに基づいて翻訳された項目と配列の構造を示す装飾項目で構成されています。装飾は配列の値を中カッコ ({ and }) で囲んだものとつぎの項目との間を区切り文字で区切ったものです。区切り文字は通常カンマ (,) ですがほかの文字でもかまいません。配列の要素の型 typdelim を設定することで決まります。 (PostgreSQL 配布物における標準のデータ型の中で box 型はセミコロン (;) を使いますがそのほかすべてはカンマを使っています。)多次元配列ではそれぞれの次元(列、面、立体など)はそれ自身の階層において中カッコと、同じ階層の中カッコで括られたつぎの塊との間に区切り文字が書かれていなくてはいけません。カッコの左側や右側の前後、あるいは独立したどんな文字列の項目の前にに空白を入れてもかまいません。 項目の後に挿入された空白は無視されませんが、空白を読み飛ばした後ではつぎの右カッコか区切り文字が来るまでのすべては項目の値と見なされます。

配列要素の引用. 上で示したように配列の値を記載するときはすべての個別の配列要素も二重カッコで括らなければなりません。もしも配列の値が配列の値の構文解析に混乱を与えるようであれば 必ずそのようにしなければなりません。例えば中カッコやカンマ(あるいはどんな区切り文字でも)、二重カッコ、逆スラッシュあるいは空白が前に付いている要素は二重カッコで括られなければなりません。配列要素の中に二重カッコを挿入する場合はその前に逆スラッシュを付けます。別の方策として配列構文ととらえられそうなすべてのデータ文字を保護したい場合や無視してもよい空白のためには逆スラッシュエスケープを使います。

空の文字列や中カッコや区切り文字、二重カッコ、逆スラッシュあるいは空白が含まれていると要素の値が配列出力処理で二重カッコで括られます。要素の値に組み込まれている二重カッコと逆スラッシュは逆スラッシュエスケープされます。 数値データ型に対しては二重カッコが出現しないと想定するのが安全ですが、テキストデータ型の場合引用がある場合とない場合に対処できるようにしておくべきです。(これは PostgreSQL リリース 7.2 以前からの振舞の変更です。

Tip: SQL コマンドで書いたものはまずリテラル文字列に翻訳され、引きつづいて配列に翻訳されることを思い出してください。必要となる逆スラッシュの数は倍増されます。例えば逆スラッシュと二重カッコがある text 型配列値を挿入するにはつぎのように書かなければなりません。

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

文字列リテラルプロセッサは配列値構文解析に渡されたとき {"\\","\""} のように見えるようにするため始めの逆スラッシュを取り除きます。その代わりとして text データ型入力処理に与えられる文字列はそれぞれ \" になります。 (たとえば bytea のように、入力処理で逆スラッシュを特別に扱うデータ型を使用すると、保存されている配列要素にひとつの逆スラッシュを挿入するために、コマンドに 8 つもの逆スラッシュが必要になることがあります。)