範囲タイプ - PolarDB - Alibaba Cloud ドキュメントセンター

このトピックでは、範囲タイプの定義と構文について説明します。

範囲型は、ある要素型 (範囲のサブタイプと呼ばれる) の値の範囲を表すデータ型です。例えば、タイムスタンプの範囲は、会議室が予約されている時間の範囲を表すために使用され得る。この場合、データ型はtsrange (「timestamp range」の略) で、timestampはサブタイプです。サブタイプは、要素値が値の範囲内にあるか、前にあるか、後にあるかを明確に定義できるように、全体の順序を持つ必要があります。

範囲型は、単一の範囲値で多くの要素値を表すため、および重複する範囲などの概念を明確に表現できるため、便利です。スケジューリング目的のための時間および日付範囲の使用は最も明確な例であるが、価格範囲、機器からの測定範囲なども有用であり得る。

組み込みレンジとマルチレンジタイプ

PostgreSQLには、次の組み込み範囲タイプがあります。

int4range-整数の範囲、int4multirange -対応するマルチレンジ
int8range-bigintの範囲、int8multirange -対応するマルチレンジ
numrange-numericの範囲、numultirange -対応するマルチレンジ
tsrange-timestamp without time zoneの範囲、tsmultirange -対応するMultirange
tstzrange-タイムスタンプとタイムゾーンの範囲、tstzmultirange -対応するマルチレンジ
daterange-dateの範囲、datemultirange -対応するマルチレンジ

さらに、独自の範囲タイプを定義できます。詳細については、「CREATE TYPE」をご参照ください。

例

CREATE TABLE予約 (部屋int、tsrange中);
    予約値に挿入する
        (1108、'[2010-01-01 14:30, 2010-01-01 15:30)');

    -- 封じ込め
    SELECT int4range (10, 20) @> 3;

    -オーバーラップ
    SELECT numrange(11.1、22.2) && numrange(20.0、30.0);

    -上限を抽出する
    SELECTアッパー (int8range(15、25));

    -- 交差を計算する
    SELECT int4range(10、20) * int4range(15、25);

    -範囲は空ですか?
    SELECT isempty(numrange(1、5));

包括的および排他的境界

すべての空でない範囲には、下限と上限の2つの境界があります。これらの値の間のすべての点が範囲に含まれる。包括的境界は、境界点自体も範囲に含まれることを意味し、排他的境界は、境界点が範囲に含まれないことを意味する。

範囲のテキスト形式では、包括的下限は「 [] 」で表され、排他的下限は「 () 」で表される。同様に、包括的な上限は「] で表され、排他的な上限は「) 」で表される。

関数lower_incおよびupper_incは、それぞれ、範囲値の下限および上限の包含性をテストする。

無限 (無制限) の範囲

範囲の下限は省略することができ、上限未満の全ての値、例えば (,3] が範囲に含まれることを意味する。同様に、範囲の上限が省略される場合、下限よりも大きいすべての値が範囲に含まれる。下限と上限の両方を省略すると、要素型のすべての値が範囲内にあると見なされます。欠けている境界を包括的として指定すると、自動的に排他的に変換されます。たとえば、[,] は (,) に変換されます。これらの欠落している値は +/-無限大と考えることができますが、それらは特別な範囲タイプの値であり、任意の範囲要素タイプの +/-無限大の値を超えていると見なされます。

「無限大」の概念を有する要素型は、それらを明示的な境界値として使用することができる。たとえば、タイムスタンプ範囲では、[today,infinity) は特別なタイムスタンプ値infinityを除外しますが、[today,infinity] は [today,) や [today,] と同様にそれを含みます。

関数lower_infとupper_infは、それぞれ範囲の無限の下限と上限をテストします。

レンジ入力 /出力

範囲値の入力は、次のいずれかのパターンに従う必要があります。

(下限、上限)
    (下限、上限)
    [lower-bound,upper-bound)
    [下限、上限]
    空の

括弧または括弧は、前述のように、下限および上限が排他的または包括的であるかどうかを示す。最後のパターンは空で、空の範囲 (点を含まない範囲) を表します。

下限は、サブタイプに有効な入力である文字列、または下限がないことを示す空の文字列のいずれかです。同様に、上限は、サブタイプに有効な入力である文字列、または上限がないことを示す空の文字列のいずれかです。

各バインド値は、"(二重引用) 文字を使用して引用できます。これは、括弧、括弧、コンマ、二重引用符、またはバックスラッシュが含まれている場合に必要です。そうでなければ、これらの文字は範囲構文の一部として使用されるためです。引用された境界値に二重引用符またはバックスラッシュを入れるには、その前にバックスラッシュを付けます。 (また、二重引用符の境界値内の二重引用符のペアは、SQLリテラル文字列の一重引用符のルールと同様に、二重引用符の文字を表すために使用されます。) または、引用を避け、バックスラッシュエスケープを使用して、範囲構文として使用されるすべてのデータ文字を保護することもできます。また、空の文字列であるバインド値を書き込むには、"" を書きます。

範囲値の前後に空白は使用できますが、括弧または括弧の間の空白は、下限値または上限値の一部として使用されます。 (要素のタイプに応じて、重要な場合と重要でない場合があります。)

重要

これらのルールは、複合型リテラルにフィールド値を書き込む場合と非常によく似ています。

例

-3を含み、7を含まず、間のすべてのポイントを含みます
    SELECT '[3,7)'::int4range;

    -3または7は含まれませんが、間のすべてのポイントが含まれます
    SELECT '(3,7)'::int4range;

    -シングルポイント4のみを含む
    SELECT '[4,4]'::int4range;

    -ポイントは含まれません (「空」に正規化されます)
    SELECT '[4,4)'::int4range;

レンジとマルチレンジの構築

各範囲型には、範囲型と同じ名前のコンストラクタ関数があります。コンストラクタ関数を使用すると、範囲リテラル定数を書くよりも便利なことがよくあります。これは、境界値の余分な引用が不要になるためです。コンストラクタ関数は2つまたは3つの引数を受け入れます。 2引数形式は標準形式 (下限を含む、上限を除く) で範囲を構成し、3引数形式は3番目の引数で指定された形式の境界を持つ範囲を構成します。 3番目の引数は、文字列「 () 」、「 (] 」、「 [) 」、または「 [] 」のいずれかでなければなりません。設定例：

-完全な形式は、下限、上限、およびを示すテキスト引数です。
    SELECT numrange(1.0, 14.0, '(]');

    -3番目の引数を省略した場合は、'[)' が仮定されます。
    SELECT numrange(1.0、14.0);

    -- ここでは '(]' が指定されていますが、表示時には値が
    SELECT int8range(1, 14, '(]');

    − いずれかの境界にNULLを使用すると、その側で範囲が制限されない。
    SELECT numrange(NULL、2.2);

離散範囲タイプ

離散範囲は、整数や日付など、要素タイプが明確に定義された「ステップ」を持つ範囲です。これらのタイプでは、2つの要素の間に有効な値がない場合、2つの要素が隣接していると言えます。これは、2つの値の間の他の要素値を常に (またはほとんど常に) 識別できる連続範囲とは対照的です。例えば、numeric型の範囲は連続的であり、timestampの範囲も連続的である。 (タイムスタンプの精度は限られているため、理論的には離散として扱うことができますが、通常はステップサイズには関心がないため、連続と見なす方が良いでしょう。)

離散範囲タイプについて考える別の方法は、各要素値に対して「次の」または「前の」値の明確な考えがあるということです。それを知っていると、最初に与えられたものの代わりに次または前の要素値を選択することによって、範囲の境界の包括的表現と排他的表現の間で変換することができます。たとえば、整数範囲では、[4,8] 型と (3,9) 型は同じ値のセットを示しますが、数値を超える範囲ではそうではありません。

離散範囲タイプには、要素タイプに必要なステップサイズを認識した正規化関数が必要です。正規化機能は、レンジタイプの等価値を、同一の表現、特に一貫して包括的または排他的な境界を有するように変換することを担う。正規化関数が指定されていない場合、フォーマットが異なる範囲は、実際には同じ値のセットを表す場合でも、常に等しくないものとして扱われます。

組み込みの範囲タイプint4range、int8range、およびdaterangeはすべて、下限を含み、上限を除外する標準形式を使用します。つまり、[) です。ただし、ユーザー定義の範囲タイプは他の規則を使用できます。

新しい範囲タイプの定義

ユーザーは独自の範囲タイプを定義できます。これを行う最も一般的な理由は、組み込みの範囲タイプの中で提供されていないサブタイプに対して範囲を使用することです。たとえば、サブタイプfloat8の新しい範囲タイプを定義するには:

CREATE TYPE floatrange AS RANGE (
        サブタイプ=float8、
        subtype_diff = float8mi
    );

    SELECT '[1.234, 5.678]'::floatrange;

float8には意味のある「ステップ」がないため、この例では正規化関数を定義しません。

独自の範囲タイプを定義すると、使用する別のサブタイプBツリー演算子クラスまたは照合順序を指定して、特定の範囲に該当する値を決定するソート順序を変更することもできます。

サブタイプが連続値ではなく離散値であると見なされる場合、CREATE TYPEコマンドでcanonical関数を指定する必要があります。正規化関数は入力範囲値を受け取り、異なる境界と書式を持つ可能性のある同等の範囲値を返す必要があります。同じ値のセットを表す2つの範囲 (たとえば、整数範囲 [1, 7] と [1, 8)) のカノニカル出力は、同一でなければなりません。書式が異なる2つの同等の値が常に同じ書式で同じ値にマッピングされる限り、どちらの表現を標準表現にするかは関係ありません。包含 /排他的境界フォーマットを調整することに加えて、正規化関数は、所望のステップサイズが、サブタイプが格納可能なものよりも大きい場合に、境界値を丸めることができる。例えば、タイムスタンプ上の範囲タイプは、1時間のステップサイズを有するように定義され得、その場合、正規化関数は、1時間の倍数ではなかった境界を丸めるか、またはおそらく代わりにエラーをスローする必要がある。

さらに、GiSTインデックスまたはSP-GiSTインデックスとともに使用されることを意味する任意の範囲タイプは、サブタイプ差、またはsubtype_diff関数を定義する必要があります。 (インデックスはsubtype_diffなしでも機能しますが、差分関数が提供されている場合よりも大幅に効率が低下する可能性があります。) サブタイプ差分関数は、サブタイプの2つの入力値を受け取り、float8値として表されるそれらの差分 (つまり、XマイナスY) を返します。上記の例では、関数float8miレギュラーの根底にあるfloat8マイナス演算子を使用できますが、他のサブタイプでは、何らかの型変換が必要になります。数字として違いを表現する方法についてのいくつかの創造的な考えも必要かもしれません。 subtype_diff関数は、選択された演算子クラスと照合順序によって暗示されるソート順序に可能な限り一致する必要があります。つまり、ソート順序に従って、最初の引数が2番目の引数よりも大きい場合は常に、結果が正になる必要があります。

subtype_diff関数の単純化されていない例は次のとおりです。

CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
    'SELECT EXTRACT(EPOCH FROM (x - y))'言語sql STRICT IMMUTABLE;

    CREATE TYPE timerange AS RANGE (範囲として)
        サブタイプ=時間、
        subtype_diff = time_subtype_diff
    );

    SELECT '[11:10, 23:00]'::timerange;

範囲タイプの作成の詳細については、「CREATE TYPE」をご参照ください。

インデックス作成

GiSTおよびSP-GiSTインデックスは、範囲タイプのテーブル列に対して作成できます。 GiSTインデックスは、マルチレンジ型のテーブル列に対しても作成できます。たとえば、GiSTインデックスを作成するには:

CREATE INDEX reservation_idx ON予約使用GIST (期間中);

範囲のGiSTまたはSP-GiSTインデックスは、=、&& 、<@ 、@> 、<<<, > 、-|- 、&< 、および &> の範囲演算子を含むクエリを高速化できます。

さらに、範囲タイプのテーブル列に対してBツリーおよびハッシュインデックスを作成できます。これらのインデックスタイプの場合、基本的に唯一の有用な範囲演算は等式です。対応する < および > 演算子を用いて、範囲値に対して定義されたBツリーソート順序付けがあるが、順序付けはむしろ任意であり、現実世界では通常有用ではない。範囲タイプのBツリーとハッシュのサポートは、主に、実際のインデックスの作成ではなく、クエリの内部でソートとハッシュを可能にすることを目的としています。

範囲の制約

UNIQUEはスカラー値の自然な制約ですが、通常は範囲タイプには適していません。代わりに、除外制約がより適切であることが多い (CREATE TABLEを参照... 結論... 除外) 。除外制約は、範囲タイプ上の「非重複」などの制約の指定を可能にする。設定例：

テーブルの予約を作成 (
        tsrangeの間、
        排他的使用GIST (WITH &&の間)
    );

この制約により、重複する値がテーブルに同時に存在するのを防ぎます。

予約値に挿入
        ('[2010-01-01 11:30, 2010-01-01 15:00)');
    INSERT 0 1

    予約値に挿入する
        ('[2010-01-01 14:45, 2010-01-01 15:45)');
    エラー: 競合するキー値が除外制約「reservation_during_excl」に違反する
    詳細: キー (中)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) の競合
    既存のキー (中)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")) 。

btree_gist拡張機能を使用して、プレーンスカラーデータ型の除外制約を定義し、範囲の除外と組み合わせて最大限の柔軟性を実現できます。たとえば、btree_gistがインストールされた後、次の制約は、会議室番号が等しい場合にのみ、重複する範囲を拒否します。

拡張の作成btree_gist;
    テーブルroom_reservationの作成 (
        部屋のテキスト、
        tsrangeの間、
        排他的使用GIST (部屋WITH=、WITH &&中)
    );

    room_reservation値に挿入する
        ('123A' 、'[2010-01-01 14:00、2010-01-01 15:00)');
    INSERT 0 1

    room_reservation値に挿入する
        ('123A' 、'[2010-01-01 14:30, 2010-01-01 15:30)');
    エラー: 競合するキー値は除外制約「room_reservation_room_during_excl」に違反します
    詳細: キー (部屋、期間中)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) の競合
    既存のキー (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00"))

    room_reservation値に挿入する
        ('123B' 、'[2010-01-01 14:30, 2010-01-01 15:30)');
    挿入0 1