PostgreSQLではテーブルの列を可変長多次元配列として定義できます。あらゆる組み込み型やユーザ定義基本型、または複合型の配列も作成可能です。ドメインの配列はサポートされていません。
実際の配列の使いかたを説明するために、次のテーブルを作成します。
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はどんな場合でも大きさの制限を強要しません。
リテラル定数として配列の値を書き込むには、その要素の値を中括弧で囲みそれぞれの要素の値をカンマで区切ります(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.11により詳しい説明があります。
ではテーブルに対していくつかの問い合わせを行ってみましょう。初めに、配列の単一要素にアクセスする方法を示します。この問い合わせは第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)
配列の値を全て置き換えることができます。
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}}
配列内のある値を検索するにはそれぞれの値が検証されなければなりません。もし配列の大きさがわかっているならば手作業でも検索できます。例を挙げます。
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に記載されています。
ティップ: 配列は集合ではありません。特定の配列要素に検索をかけることはデータベース設計が誤っている可能性があります。配列の要素とみなされるそれぞれの項目を行に持つ別のテーブルを使うことを検討してください。この方が検索がより簡単になり要素数が大きくなっても規模的拡張性があります。
配列の値の外部表現は配列の要素の型に対する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という単語自体も同様に引用符で括らなければなりません。 二重引用符もしくは逆スラッシュを引用符付きの配列要素に付け加えたい場合、エスケープ文字列構文を使用し、そしてその直前に逆スラッシュを付けます。別の方法として配列構文とみなされるような全てのデータ文字を、引用符を使用しないで逆スラッシュでエスケープしても構いません。
括弧の右側もしくは左側それぞれの前と後に空白を追加することができます。同様に独立した項目の文字列の前後に空白を付け加えることもできます。これら全ての場合において空白は無視されます。とは言っても二重引用符で囲まれた要素の中の空白、もしくは要素の空白文字以外により両側が括られているものは無視されません。
注意: SQLコマンドで書かれたものは最初に文字列リテラルとして解釈され、その次に配列として解釈されることを覚えておいてください。と言うことは、逆スラッシュの数が倍になることを意味します。例えば逆スラッシュと二重引用符を含んだtext配列値を挿入する場合、次のようになります。
INSERT ... VALUES (E'{"\\\\","\\""}');エスケープ文字列プロセッサは1つの階層の逆スラッシュを取り除きますので、配列値パーサに渡された時は{"\\","\""}のようになります。その反対にtextデータ型入力ルーチンに与えられた文字列はそれぞれ\と"になります(もし入力ルーチンが逆スラッシュを特別に取り扱うデータ型を操作している場合(例えばbytea)、1つの逆スラッシュを配列要素に保存したい時はコマンドの中に8つの逆スラッシュが必要です)。 ドル引用符付け(項4.1.2.4参照)を使用して、バックスラッシュを二重化する必要性をなくすことができます。
ティップ: SQLコマンドの中で配列値を書く時、配列リテラル構文よりもARRAY生成子構文(項4.2.11を参照)の方が往々にして扱いやすい場合があります。