PolarDB:配列

このトピックでは、配列の定義と構文について説明します。

定義

配列型の使用を説明するために、次のテーブルを作成します。

CREATE TABLE sal_emp (
        テキスト名、
        pay_by_quarter integer[],
        スケジュールテキスト [][][]
    );

図に示すように、配列データ型は、配列要素のデータ型名に角括弧 ([]) を追加することによって名前が付けられます。 上記のコマンドは、text (name) 型の列、従業員の給与を四半期ごとに表す整数型の1次元配列 (pay_by_quarter) 、およびtext (schedule) の2次元配列を持つsal_empという名前のテーブルを作成します。CREATE TABLEの構文では、次のように配列の正確なサイズを指定できます。

CREATE TABLE tictactoe (
        平方整数 [3][3]
    );

ただし、現在の実装では、指定された配列サイズの制限は無視されます。つまり、動作は、指定されていない長さの配列と同じです。 特定の要素タイプの配列は、サイズまたは次元数にかかわらず、すべて同じタイプであると見なされます。 したがって、CREATE TABLEで配列サイズまたはディメンション数を宣言することは、単なるドキュメントであり、実行時の動作には影響しません。 キーワードARRAYを使用してSQL標準に準拠する代替構文を1次元配列に使用できます。 pay_by_quarterは次のように定義できます。

pay_by_quarter整数ARRAY[4],

または、配列サイズを指定しない場合:

pay_by_quarter整数ARRAY,

ただし、前述のように、PostgreSQLはサイズ制限を適用しません。

配列値の入力

配列値をリテラル定数として記述するには、要素値を中括弧内に囲み、コンマで区切ります。 (Cがわかっている場合、これは構造を初期化するためのC構文と同じです。) 任意の要素値の周りに二重引用符を付けることができますが、コンマまたは中括弧が含まれている場合は二重引用符を付ける必要があります。 (詳細は以下に示します。) したがって、配列定数の一般的な形式は次のとおりです。

'{ val1 delim val2 delim ... }'

ここで、delimは、pg_typeエントリに記録されているタイプの区切り文字です。 PostgreSQLディストリビューションで提供される標準データ型の中で、セミコロン (;) を使用するタイプボックスを除いて、すべてコンマ (,) を使用します。 各valは、配列要素型の定数またはサブ配列です。 配列定数の例は次のとおりです。

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

この定数は、整数の3つのサブアレイからなる2次元の3 × 3のアレイである。

配列定数の要素をNULLに設定するには、要素値にNULLを書き込みます。 (NULLwillの大文字または小文字のバリアントはすべて行います。) 実際の文字列値「NULL」が必要な場合は、その周りに二重引用符を付ける必要があります。

これで、INSERTステートメントをいくつか表示できます。

はsal_empに挿入します
        バリュー (「ビル」、
        '{10000, 10000, 10000, 10000}',
        '{{"meeting", "lunch"}, {"training", "presentation"}}');

    sal_empに挿入する
        バリュー (「キャロル」、
        '{20000, 25000, 25000, 25000}',
        '{{"breakfast", "consulting"}, {"meeting", "lunch"}}'); 

前の2つの挿入の結果は次のようになります。

SELECT * からsal_emp;
     name | pay_by_quarter | スケジュール
    -------+---------------------------+-------------------------------------------
     請求書 | {10000,10000、10000,10000} | {{meeting,lunch },{ training,presentation}}
     キャロル | {20000,25000、25000,25000} | {{breakfast,consulting },{ meeting,lunch}}
    (2行) 

多次元配列は、次元ごとに一致するエクステントを持つ必要があります。 不一致はエラーの原因となります。

はsal_empに挿入します
        バリュー (「ビル」、
        '{10000, 10000, 10000, 10000}',
        '{{"meeting", "lunch"}, {"meeting"}}');
    エラー: 多次元配列には、一致するディメンション

を持つ配列式が必要です

ARRAYコンストラクター構文も使用できます。

はsal_empに挿入します
        バリュー (「ビル」、
        ARRAY[10000、10000、10000、10000] 、
        ARRAY[['meeting' 、'lunch'] 、['training' 、'presentation']]);

    sal_empに挿入する
        バリュー (「キャロル」、
        ARRAY[20000、25000、25000、25000] 、
        ARRAY[['breakfast' 、'consulting '] 、['meeting' 、'lunch']]); 

配列要素は通常のSQL定数または式であることに注意してください。たとえば、文字列リテラルは、配列リテラルのように二重引用符で囲まれるのではなく、単一引用符です。

配列へのアクセス

これで、テーブルに対していくつかのクエリを実行できます。 まず、配列の単一の要素にアクセスする方法を示します。 このクエリは、第2四半期に給与が変更された従業員の名前を取得します。

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

     name
    -------
     キャロル
    (1行) 

配列の下付き数字は角括弧内に書かれている。 既定では、PostgreSQLは配列に1つのベースの番号付け規則を使用します。つまり、nelementsの配列はarray[1] で始まり、array[] で終わります。

このクエリは、すべての従業員の第3四半期の給与を取得します。

SELECT pay_by_quarter[3] FROM sal_emp;

     pay_by_quarter
    ----------------
              10000
              25000
    (2行) 

配列またはサブ配列の任意の長方形スライスにアクセスすることもできます。 アレイスライスは、1つ以上のアレイ次元について下限: 上限を書くことによって示される。 たとえば、このクエリは、週の最初の2日間のBillのスケジュールの最初の項目を取得します。

SELECTスケジュール [1:2][1:1] sal_empから名前='Bill';

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

いずれかの次元がスライスとして書き込まれる場合、すなわちコロンを含む場合、すべての次元はスライスとして扱われる。 単一の数値 (コロンなし) のみを持つディメンションは、1から指定された数値までのものとして扱われます。 たとえば、[2] はこの例のように [1:2] として扱われます。

SELECTスケジュール [1:2][2] sal_empから名前='Bill';

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

非スライスの場合との混同を避けるために、すべてのディメンションにスライス構文を使用することをお勧めします。例: [2][1:1] ではなく [1:2][1:1] です。 スライス指定子の下限および /または上限を省略することができます。欠落している境界は、アレイの下付き文字の下限または上限に置き換えられます。 設定例：

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

            schedule
    ------------------------
     {{lunch },{ presentation}}
    （1行）

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

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

配列自体またはいずれかの下付き文字式がnullの場合、配列下付き文字式はnullを返します。 また、添え字が配列の境界外にある場合はnullが返されます (この場合はエラーは発生しません) 。 たとえば、scheduleのディメンションが現在 [1:3][1:2] の場合、schedule[3][3] を参照するとNULLが生成されます。 同様に、誤った数の添字を有するアレイ参照は、エラーではなくヌルを生じる。

配列自体または下付き文字式のいずれかがnullである場合、配列スライス式も同様にnullを生成します。 ただし、現在の配列境界の完全に外側にある配列スライスを選択するなどの他の場合では、スライス式はnullではなく空 (ゼロ次元) の配列を生成します。 (これは、非スライス動作と一致せず、履歴上の理由で行われる。) 要求されたスライスがアレイ境界と部分的に重複する場合、ヌルを返す代わりに、重複領域のみに黙って縮小される。

任意の配列値の現在のディメンションは、array_dims関数で取得できます。

SELECT array_dims (スケジュール) FROM sal_emp WHERE name = 'Carol';

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

array_dimsはテキスト結果を生成します。これは、人々が読むのに便利ですが、プログラムにとってはおそらく不便です。 ディメンションは、array_upperとarray_lowerで取得することもできます。これらは、指定した配列ディメンションの上限と下限をそれぞれ返します。

SELECT array_upper (スケジュール、1) FROM sal_emp WHERE name = 'Carol';

     array_upper
    -------------
               2
    (1行) 

array_lengthは、指定した配列ディメンションの長さを返します。

SELECT array_length (スケジュール、1) FROM sal_emp WHERE name = 'Carol';

     array_length
    --------------
                2
    (1行) 

cardinalityは、すべての次元にわたる配列内の要素の総数を返します。 これは、事実上、unnestへの呼び出しがもたらす行の数です。

SELECTカーディナリティ (スケジュール) FROM sal_emp WHERE name = 'Carol';

     cardinality
    -------------
               4
    (1行) 

配列の変更

配列値は完全に置き換えることができます:

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

配列は、単一の要素で更新することもできます。

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ベースのサブスクリプトを使用しない配列を作成できます。 たとえば、myarray[-2:7] に割り当てて、-2から7の下付き文字の値を持つ配列を作成できます。

新しい配列値は、連結演算子 | | を使用して構築することもできます。

SELECT ARRAY[1,2] | | ARRAY[3,4];
     ? コラム?
    -----------
     {1,2,3,4}
    （1行）

    SELECT ARRAY[5,6] | | ARRAY[[1,2],[3,4]]];
          ? コラム?
    ---------------------
     {{5,6 },{ 1,2 },{ 3,4}}
    (1行) 

連結演算子は、単一の要素を1次元配列の始めまたは終わりにプッシュすることを可能にする。 また、2つのN次元アレイ、またはN次元およびN + 1次元アレイを受け入れる。

1つの要素が1次元配列の先頭または末尾にプッシュされると、結果は配列オペランドと同じ下界の下付き文字を持つ配列になります。 設定例：

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

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

等しい数の次元を持つ2つの配列が連結された場合、結果は左側のオペランドの外次元の下界の添字を保持します。 結果は、左側のオペランドのすべての要素とそれに続く右側のオペランドのすべての要素を含む配列です。 設定例：

SELECT array_dims(ARRAY[1,2] | | ARRAY[3,4,5]);
     array_dims
    ------------
     [1:5]
    （1行）

    SELECT array_dims(ARRAY[[1,2],[3,4]] | | ARRAY[[5,6],[7,8],[9,0]]);
     array_dims
    ------------
     [1:5][1:2]
    (1行) 

N次元配列がN + 1次元配列の始めまたは終わりにプッシュされると、結果は上記の要素配列の場合と同様になる。 各N次元サブアレイは、本質的に、N + 1次元アレイの外次元の要素である。 設定例：

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

配列は、array_prepend、array_append、またはarray_catの関数を使用して構築することもできます。 最初の2つは1次元配列のみをサポートしますが、array_catは多次元配列をサポートします。 いくつかの例:

SELECT array_prepend(1, ARRAY[2,3]);
     array_prepend
    ---------------
     {1,2,3}
    （1行）

    SELECT array_append(ARRAY[1,2], 3);
     array_append
    --------------
     {1,2,3}
    （1行）

    SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
     array_cat
    -----------
     {1,2,3,4}
    （1行）

    SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
          array_cat
    ---------------------
     {{1,2 },{ 3,4 },{ 5,6}}
    （1行）

    SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
          array_cat
    ---------------------
     {{5,6 },{ 1,2 },{ 3,4}} 

単純な場合には、上述の連結演算子は、これらの関数を直接使用するよりも好ましい。 しかし、連結演算子は3つのケースすべてを処理するためにオーバーロードされるため、関数の1つを使用するとあいまいさを避けるのに役立つ状況があります。 例えば考慮しなさい:

SELECT ARRAY[1, 2] | | '{3, 4}'; -- 型指定されていないリテラルは配列として取得されます
     ? コラム?
    -----------
     {1,2,3,4}

    SELECT ARRAY[1, 2] | | '7'; -- so is this one
    エラー: 不正な配列リテラル: "7"

    SELECT ARRAY[1, 2] | | NULL; -- soは装飾されていないNULLです
     ? コラム?
    ----------
     {1,2}
    （1行）

    SELECT array_append(ARRAY[1, 2], NULL); -これは意味されている可能性があります
     array_append
    --------------
     {1,2,NULL} 

上記の例では、パーサーは、連結演算子の一方の側に整数配列を、他方の側に未定型の定数を見る。 定数の型を解決するために使用するヒューリスティックは、演算子の他の入力 (この場合は整数配列) と同じ型であると仮定することです。 したがって、連結演算子は、array_appendではなく、array_catを表すと仮定されます。 それが間違った選択の場合、定数を配列の要素型にキャストすることで修正できます。ただし、array_appendを明示的に使用することが望ましい解決策かもしれません。

配列での検索

配列内の値を検索するには、各値をチェックする必要があります。 配列のサイズがわかっていれば、これは手動で行うことができます。 設定例：

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                                pay_by_quarter[2] = 10000または
                                pay_by_quarter[3] = 10000または
                                pay_by_quarter[4] = 10000; 

しかし、これは、大きなアレイに対してはすぐに面倒になり、アレイのサイズが未知である場合には役に立たない。 上記のクエリは、次のように置き換えることができます。

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; 

&& 演算子を使用して配列を検索することもできます。この演算子は、左オペランドが右オペランドと重複するかどうかを確認します。 例えば:

SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];

array_positionおよびarray_positions関数を使用して、配列内の特定の値を検索することもできます。 前者は、配列内の値の最初の出現の下付き文字を返します。後者は、配列内の値のすべての出現の下付き文字を持つ配列を返します。 設定例：

SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
     array_position
    ----------------
                  2
    （1行）

    SELECT array_positions(ARRAY[1、4、3、1、3、4、2、1] 、1);
     array_positions
    -----------------
     {1,4,8}
    (1行) 

配列入出力構文

配列値の外部テキスト表現は、配列の要素型のI/O変換規則に従って解釈される項目と、配列構造を示す装飾で構成されます。 装飾は、配列値の周りの中括弧 ({および}) と、隣接するアイテム間の区切り文字で構成されます。 区切り文字は通常、コンマ (,) ですが、他のものにすることもできます。これは、配列の要素型のtypdelim設定によって決定されます。 PostgreSQLdistributionで提供される標準データ型の中で、セミコロン (;) を使用するタイプボックスを除いて、すべてコンマを使用します。 多次元配列では、各次元 (行、平面、立方体など) は独自のレベルの中括弧を取得し、同じレベルの隣接する中括弧エンティティ間に区切り文字を記述する必要があります。

配列出力ルーチンは、要素値が空の文字列、中括弧、区切り文字、二重引用符、バックスラッシュ、または空白を含む場合、または単語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行) 

アレイ出力ルーチンは、1とは異なる1つ以上の下限がある場合にのみ、その結果に明示的な次元を含む。

要素に書き込まれた値がNULLの場合 (いずれの場合もバリアント) 、その要素はNULLになります。 引用符またはバックスラッシュが存在すると、これは無効になり、リテラル文字列値「NULL」を入力できます。 また、pre-8.2のバージョンのPostgreSQLとの下位互換性のために、thearray_nullsconfigurationパラメーターをオフにして、NULLがNULLとして認識されないようにすることもできます。

前述のように、配列値を書き込むときは、個々の配列要素の周りに二重引用符を使用できます。 要素値が配列値パーサーを混乱させる場合は、そうしなければなりません。 たとえば、中括弧、コンマ (またはデータ型の区切り文字) 、二重引用符、バックスラッシュ、または先頭または末尾の空白を含む要素は、二重引用符で囲まれている必要があります。 空の文字列と単語NULLに一致する文字列も引用する必要があります。 引用された配列要素の値に二重引用符またはバックスラッシュを入れるには、その前にバックスラッシュを付けます。 または、引用符を避け、バックスラッシュエスケープを使用して、配列構文として使用されるすべてのデータ文字を保護することもできます。

左ブレースの前または右ブレースの後に空白を追加できます。 個々のアイテム文字列の前後に空白を追加することもできます。 これらすべての場合、空白は無視されます。 ただし、二重引用符付き要素内の空白、または要素の非空白文字で両側を囲まれた空白は無視されません。