8.14. 配列

8.14.1. 配列型の宣言

実際に配列の使い方を説明するために、次のテーブルを作成します。

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

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

CREATE TABLE構文で指定する配列の正確な大きさを指定することができます。

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

とは言っても現在の実装では指定された配列の大きさの制限を無視します。 つまり、長さの指定がない配列と同じ振舞いをします。

現在の実装では次元数の宣言も強制していません。 特定の要素型の配列はすべて大きさあるいは次元数とは無関係に同じ型とみなされます。 ですからCREATE TABLEで配列の大きさや次元数を宣言することは、単なる説明です。 実行時の動作に影響を及ぼしません。

SQLに準拠し、ARRAYキーワードを使用したもう1つの構文を一次元配列に使うことができます。 pay_by_quarterを次のように定義することもできます。

    pay_by_quarter  integer ARRAY[4],

または、もし配列の大きさが指定されない場合は次のようになります。

    pay_by_quarter  integer ARRAY,

しかし、前で触れたようにPostgreSQLはどんな場合でも大きさの制限を強要しません。

8.14.2. 配列の値の入力

リテラル定数として配列の値を書き込むには、その要素の値を中括弧で囲み、それぞれの要素の値をカンマで区切ります （C言語を知っているならば、構造体を初期化するための構文のようなものと考えてください）。 要素の値を二重引用符でくくることもでき、カンマもしくは中括弧がある時は必ずそのように書かなければなりません （詳細は以下に出てきます）。 したがって配列定数の一般的書式は次のようになります。

'{ val1 delim val2 delim ... }'

ここでdelimはそのpg_type項目に記録されている型の区切り文字です。 PostgreSQL配布物で提供されている標準データ型の内、セミコロン（;）を使用するbox型を除き、すべてはカンマ（,）を使います。 それぞれのvalは配列要素の型の定数か副配列です。 配列定数の例を以下に示します。

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

この定数は整数の3つの副配列を持っている二次元3×3の配列です。

配列定数の要素をNULLとするためには、その要素値にNULLと記載してください。 （NULLを大文字で書いても小文字で書いても構いません。） "NULL"という文字列値を指定したければ、二重引用符でくくって記載しなければなりません。

（この種の配列定数は実際項4.1.2.7で説明されている一般型定数の特別の場合に過ぎません。 この定数は元々文字列として扱われていて配列入力ルーチンに渡されます。 明示的な型指定が必要かもしれません。）

では、INSERT文をいくつか紹介します。

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

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

上に記載した2つの挿入文の結果は次のようになります。

SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

多次元配列では、各次元の範囲を合わせなければなりません。 一致しないと以下のようにエラーが発生します。

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  multidimensional arrays must have array expressions with matching dimensions

ARRAY生成子構文も使えます。

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

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

配列要素は通常のSQL定数もしくは演算式であることに注意してください。 例えば文字列リテラルは配列リテラルと同様、二重引用符ではなく単一引用符でくくられます。 ARRAY生成子構文は項4.2.12により詳しい説明があります。

8.14.3. 配列へのアクセス

ではテーブルに対していくつかの問い合わせを行ってみましょう。 初めに、配列の単一要素にアクセスする方法を示します。 この問い合わせは第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)

また、配列や副配列の任意の縦方向の部分を切り出すこともできます。 一次元以上の配列についてその一部を表現するには、lower-bound:upper-boundと記述します。 例えばこの問い合わせはBillのその週の初めの2日に最初何が予定されているかを抽出します。

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

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

任意の次元を部分として、つまりコロンを含めて記述すると、すべての次元が部分として扱われます。 単一の番号のみ（コロンを持たない）を持つ次元はすべて、1から指定番号までと扱われます。 例えば、[2]は以下の例のように [1:2]と扱われます。

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

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

切り出しのない場合と混乱を避けるため、すべての次元に対し切り出し構文を使用することが最善です。 例えば、[2][1:1]ではなく、[1:2][1:1]のようにします。

配列自体がNULLもしくはその添字式がNULLとなる場合、配列添字式はNULLを返します。 また、配列の範囲を超える添字の場合もNULLが返されます（この場合はエラーになりません）。 例えば、scheduleが現在[1:3][1:2]次元であれば、schedule[3][3]の参照はNULLとなります。 同様にして、添字として間違った値を指定して配列を参照した場合もエラーではなく、NULLが返されます。

同様に、部分配列式も配列自体がNULLもしくはその添字式がNULLとなる場合にNULLを返します。 しかし、現在の配列範囲を完全に超えた部分配列を選択する場合では、部分配列式はNULLではなく空の（0次元）の配列を返します。 （これは切り出しなしの動作に一致せず、歴史的理由で行われるものです。） 要求された部分配列が配列の範囲に重なる場合、NULLを返さずに、警告なく重複部分だけに減少させます。

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

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

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

array_dims関数はtext型で結果を返します。 人間が結果を見るためには便利ですが、プログラムにとって都合がよくありません。 次元はarray_upperとarray_lowerでも抽出することができ、それぞれ特定の配列の次元の上限と下限を返します。

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

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

array_lengthは指定された配列次元の長さを返します。

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

 array_length
--------------
            2
(1 row)

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

配列の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';

保存されている配列の値は、存在しない要素に代入することで拡張することができます。 過去に存在した位置と新しく代入された位置との間はNULLで埋められます。 例えば、現在配列myarrayの要素数が4の場合、myarray[6]を割り当てる更新の後6要素を持つことなり、myarray[5]はNULLを含みます。 現在、こうした方法での拡張は、1次元配列でのみ許されます。 多次元配列では行うことができません。

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

新規の配列の値は連結演算子||を用いて作成することもできます。

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)

連結演算子を使うと、一次元配列の最初もしくは最後に1つの要素を押し込むことができます。 さらには2つのN-次元配列もしくはN-次元配列とN+1-次元配列にも対応しています。

1つの要素が1次元配列の先頭や末尾に押し込まれた時、結果は配列演算項目と同じ下限添字を持つ配列となります。 以下に例を示します。

SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
 array_dims
------------
 [0:2]
(1 row)

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

等しい次元を持った2つの配列が連結された場合、結果は左側演算項目の外側の次元の下限添字を引き継ぎます。 結果は右側被演算子のすべての要素に左側被演算子が続いた配列となります。 例を挙げます。

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-次元副配列は本質的にN+1-次元配列の外側の次元の要素となります。 例を挙げます。

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

配列はarray_prepend、array_append、もしくはarray_catを使って構築することもできます。 初めの2つは一次元配列にしか対応していませんが、array_catは多次元配列でも使えます。 上で説明した連結演算子はそれぞれの関数を直接実行することよりも望ましいことを覚えておいてください。 事実、それらの関数は連結演算子の実装のため主として存在します。 とは言っても、ユーザ定義の集約関数を作る時そのまま使えます。 例を挙げます。

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.14.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;

とは言ってもこの方法では大きい配列では大変な作業となりますし、配列の大きさが不明な場合この方法は使えません。 代わりになる方法が項9.21で説明されています。 上の問い合わせは以下のように書くことができます。

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

さらに配列で行の値が全て10000に等しいものを見つけることもできます。

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

代わりとして、generate_subscripts関数を使うことができます。 以下はその例です。

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

この関数は表9-46に記載されています。

8.14.6. 配列の入出力構文

配列の値の外部表現は配列の要素の型に対するI/O変換ルールに基づいて解釈された項目と配列の構造を示す装飾項目で構成されています。 装飾は配列の値を中括弧（{と}）で囲んだものと次の項目との間を区切り文字で区切ったものです。 区切り文字は通常カンマ（,）ですが他の文字でも構いません。 配列の要素の型typdelimを設定することで決まります。 PostgreSQL配布物における標準のデータ型の中でセミコロン（;）を使うbox型を除いて、すべてはカンマを使います。 多次元配列ではそれぞれの次元（行、面、立体など）はそれ自身の階層において中括弧、同じ階層の中括弧でくくられた次の塊との間に区切り文字が書かれていなければなりません。

空の文字列や中括弧や区切り文字、二重引用符、バックスラッシュ、空白、NULLという単語が含まれていると、配列出力処理は要素の値を二重引用符でくくります。 要素の値に組み込まれている二重引用符とバックスラッシュはバックスラッシュでエスケープされます。 数値データ型に対しては二重引用符が出現しないと想定するのが安全ですが、テキストデータ型の場合引用符がある場合とない場合に対処できるようにしておくべきです。

デフォルトでは配列の次元の下限インデックス値は1に設定されています。 他の下限値を持つ配列を表現したければ、配列定数を作成する前に明示的に配列添字範囲を指定することで実現できます。 修飾項目はそれぞれの配列次元の上限と下限をコロン（:）で区切って前後を大括弧（[]）でくくった形式になっています。 代入演算子（=）の後に配列次元修飾項目が続きます。 例を示します。

SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;

 e1 | e2
----+----
  1 |  6
(1 row)

1とは異なる下限を持つ場合にのみ、配列出力関数はその結果に明示的な次元を含めます。

要素に指定された値がNULL（またはその亜種）の場合、要素はNULLとして扱われます。 引用符やバックスラッシュがあると、これは無効となり、"NULL"という文字列リテラルを入力することができます。 また、8.2以前のPostgreSQLとの後方互換性のため、array_nulls設定パラメータをoffにして、NULLをNULLとして認識しないようにすることができます。

前に示したように配列に値を書き込む場合は独立した配列要素を二重引用符でくくります。 配列値パーサが配列要素値によって混乱を来さないように必ずこの形式を守ってください。 例えば、中括弧、カンマ（もしくはデータ型の区切り文字）、二重引用符、バックスラッシュもしくは前後に付いた空白を含む要素は必ず二重引用符でくくらなければなりません。 空文字列やNULLという単語自体も同様に引用符でくくらなければなりません。 二重引用符もしくはバックスラッシュを引用符付きの配列要素に付け加えたい場合、エスケープ文字列構文を使用し、そしてその直前にバックスラッシュを付けます。 別の方法として配列構文とみなされるような全てのデータ文字を、引用符を使用しないでバックスラッシュでエスケープしても構いません。

括弧の右側もしくは左側それぞれの前と後に空白を追加することができます。 同様に独立した項目の文字列の前後に空白を付け加えることもできます。 これらすべての場合において空白は無視されます。 とは言っても二重引用符で囲まれた要素の中の空白、もしくは要素の空白文字以外により両側がくくられているものは無視されません。