8.17. 範囲型

8.17.1. 組み込みの範囲型

PostgreSQLには、以下の組み込みの範囲型があります。

• int4range — integerの範囲

• int8range — bigintの範囲

• numrange — numericの範囲

• tsrange — timestamp without time zoneの範囲

• tstzrange — timestamp with time zoneの範囲

• daterange — dateの範囲

この他にも、独自の範囲型を定義することができます。詳しくはCREATE TYPEを参照してください。

8.17.2. 例

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (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 upper(int8range(15, 25));

-- 共通部分の計算
SELECT int4range(10, 20) * int4range(15, 25);

-- 範囲は空か
SELECT isempty(numrange(1, 5));

範囲型についての演算子と関数の完全なリストについては、表9-44と表9-45を参照してください。

8.17.3. 閉じた境界と開いた境界

空でない範囲には必ず2つの境界、つまり下限値と上限値があります。 これらの値の間にある値はすべてその範囲に含まれます。 閉じた境界とは、その境界値自体が範囲に含まれることを意味し、開いた境界とは、その境界値が範囲に含まれないことを意味します。

範囲を文字列の形式で表すとき、閉じた下限値は"["で、開いた下限値は"("で表します。 同様に、閉じた上限値は"]"で、開いた上限値は")"で表します。 (詳しくは 項8.17.5を参照してください。)

関数lower_incおよびupper_incはそれぞれ、範囲の下限値と上限値が閉じているかどうかを検査します。

8.17.4. 無限の(境界のない)範囲

範囲の下限値は省略することができ、このとき、上限値より小さいすべての値はその範囲に含まれることになります。 同じように、範囲の上限値も省略することができ、このときは、下限値より大きいすべての値がその範囲に含まれることになります。 下限値と上限値が両方とも省略されたときは、その要素型のすべての値がその範囲に含まれるとみなされます。

これは、それぞれ、下限値が"マイナス無限大"である、あるいは、上限値が"プラス無限大"である、と見なすことと同等です。 ただし、これらの無限大の値は範囲型の値にはならない、ということ、そして範囲の一部になることはできない、ということに注意してください。 (つまり、閉じた無限大の境界などといったものはありません — そのように書いてもそれは自動的に開いた境界に変換されます。)

いくつかの要素型には"無限大"の概念がありますが、範囲型の機能に関する限り、これは一つの値として扱われます。 例えばtimestampの範囲で[today,]の意味は[today,)と同じです。 しかし、[today,infinity]の意味は[today,infinity)と少し違います — 後者はtimestampの特別な値infinityを含みません。

関数lower_infおよびupper_infはそれぞれ範囲の下限値と上限値が無限大かどうかを検査します。

8.17.5. 範囲の入出力

範囲値の入力は、以下の形式の一つに従わなければなりません。

(lower-bound,upper-bound)
(lower-bound,upper-bound]
[lower-bound,upper-bound)
[lower-bound,upper-bound]
empty

前にも述べたとおり、丸括弧と大括弧は下限値と上限値が開いているか閉じているかを表します。 最後の形式がemptyであることに注意してください。これは空の範囲(範囲に含まれる値が1つもない)を表します。

lower-boundは、その派生元型の有効な入力値となる文字列か、あるいは省略して下限値がないことを指定するかのいずれかです。 同様に、upper-boundは、その派生元型の有効な入力値となる文字列か、あるいは省略して上限値がないことを指定するかのいずれかです。

境界値は"(二重引用符)で括ることができます。 これは特に境界値が丸括弧、大括弧、カンマ、二重引用符、あるいはバックスラッシュを含んでいる場合に必要となります。そうしなければ、これらの文字は範囲の構文の一部とみなされてしまうからです。 二重引用符あるいはバックスラッシュを引用符で括られた境界値の中に入れるには、その直前にバックスラッシュを入れてください。 (また、SQLの文字列リテラルと同じように、二重引用符で括られた境界値の中で二重引用符を2つ続けることで1つの二重引用符を表すこともできます。) あるいは、引用符で括る代わりに、範囲の構文の一部とみなされるすべての文字をバックスラッシュでエスケープする、ということもできます。 なお、境界値として空文字列を指定するには""と書いてください。何も書かないと、境界値が無限大であることになってしまいます。

境界値の前後に空白文字を入れることができますが、括弧内にある空白文字はすべて下限値あるいは上限値の一部とみなされます。 (このことは、要素型によっては重要かもしれませんし、重要でないかもしれません。)

例：

-- 3を含み、7を含まない。その間の数はすべて含まれる
SELECT '[3,7)'::int4range;

-- 3も7も含まないが、その間の数はすべて含まれる
SELECT '(3,7)'::int4range;

-- 1つの値、4だけを含む
SELECT '[4,4]'::int4range;

-- 含まれる点は何もない('empty'に正規化される)
SELECT '[4,4)'::int4range;

8.17.6. 範囲の生成

範囲型には、その範囲型と同じ名前のコンストラクタ関数があります。 コンストラクタ関数を使うと、境界値の指定で余計な引用を使わずに済むので、リテラルの定数で範囲を記述するよりも便利なことが多いでしょう。 コンストラクタ関数は2つ、または3つの引数をとります。 引数が2つの形式では、(閉じた下限値, 開いた上限値)という標準的な形式の範囲を生成します。引数が3つの形式では、3番目の引数で指定した形式の境界の範囲を生成します。 3番目の引数は、以下の文字列のいずれかでなければなりません。 "()"、"(]"、"[)"、または"[]"。 例えば、

-- 完全な形式では、下限値、上限値、そして境界が閉じているか開いているかを
-- 示す文字列の引数を指定する
SELECT numrange(1.0, 14.0, '(]');

-- 3番目の引数が省略されると、'[)'を指定したのと同じになる
SELECT numrange(1.0, 14.0);

-- ここでは'(]'を指定しているが、int8rangeは離散的な範囲型(下記参照)なので
-- 正規化された形式に変換されて表示される
SELECT int8range(1, 14, '(]');

-- 境界値にNULLを指定すると、範囲の上限、あるいは下限がないことになる
SELECT numrange(NULL, 2.2);

8.17.7. 離散的な範囲型

離散的な範囲とは、integerやdateのように明確に定義された"ステップ"のある要素型の範囲のことです。 このような型において、2つの要素の間に有効な値が1つもないとき、その2つの要素は隣接している、と言います。 これは連続的な範囲と対照的です。連続的な範囲では、任意の2つの値について、それらの間に別の値を見つけることが、いつでも(あるいは、ほとんどいつでも)可能です。 例えば、numeric型やtimestamp型の範囲は連続的です。 (timestampの精度は限界があるので、理論的には離散的として取り扱うことも可能ですが、ステップの大きさについて関心がないのが普通ですから、連続的であると考える方が良いでしょう。)

離散的な範囲型に関するもう1つの考え方は、各要素の値について、"次"あるいは"前"の値というのものが明確に考えられるか、ということです。 これを知っていれば、範囲の境界の閉じた表現、あるいは開いた表現について、その値の次、あるいは前の値を使って、表現を変換することができます。 例えば、整数の範囲型[4,8]と(3,9)は同じ値の集合を意味しますが、これがnumericの範囲型であったならそうではありません。

離散的な範囲型はその要素型で使いたいステップのサイズを認識する正規化関数を持つべきです。 正規化関数は同等な値の範囲型を、同一の表現に、特に、閉じた境界、開いた境界について一定の形式に変換します。 正規化関数が指定されない場合、異なる形式の範囲は必ず等しくないものとして扱われます。これは例え、それらが現実的に同じ値の集合であったとしても、等しくないとされます。

組み込みの範囲型であるint4range、int8range、およびdaterangeはいずれも閉じた下限値と開いた上限値、つまり[)の正規化形式を使います。 しかし、ユーザ定義の範囲型はこれとは別の方式を使うことができます。

8.17.8. 新しい範囲型の定義

独自の範囲型を定義することもできます。 もっともありそうな理由は、組み込みの範囲型では、その派生元型についての範囲型が提供されていない、ということでしょう。 例えば、float8を派生元型とする新しい範囲型を定義するには次のようにします。

CREATE TYPE floatrange AS RANGE (
    subtype = float8,
    subtype_diff = float8mi
);

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

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

派生元型が、連続的ではなく離散的な値を持つと考えられる場合は、CREATE TYPEコマンドでcanonical(正規化)関数を指定する必要があります。 正規化関数は、範囲の値を入力として受け取り、それと同等な範囲の値を返さなければなりません。戻り値は、入力値とは異なる境界値と形式になっているかもしれません。 同じ値の集合を表す範囲、例えば、整数の範囲である[1, 7]と[1, 8)の正規化出力は、同一である必要があります。 異なる形式の同等な値が、いつでも同じ形式の同じ値に変換されるのであれば、正規化出力の形式は何であってもかまいません。 正規化関数は、閉じた境界、開いた境界の形式を調整するだけではありません。派生元型が格納できるよりも大きなサイズのステップを使いたい場合は境界値を丸めることもあります。 例えばtimestampの範囲型をステップのサイズを1時間として定義することができます。このとき、正規化関数は1時間の倍数になっていない境界値を丸める必要があります。あるいは、その代わりにエラーを投げることもできます。

独自の範囲型を定義すると、派生元型とは異なるB-tree演算子クラスや照合順を指定でき、どの値が指定の範囲に入るかを決定するソート順を変更することもできます。

また、GiSTまたはSP-GiSTインデックスと一緒に使われる範囲型は、派生元型の差分、つまりsubtype_diff関数を定義すべきです。 (そのインデックスはsubtype_diffがなくても機能しますが、差分関数が提供されている時に比べると、あまり効果的でないことが多いでしょう。) 派生元型の差分関数は、2つの派生元型の入力値をとり、その差分(つまり、X引くY)をfloat8型の値として返します。 上の例では、通常のfloat8のマイナス演算子が呼び出す関数を使うことができますが、それ以外の派生元型では何らかの型変換が必要となるでしょう。 差分をいかにして数字で表現するかについて、創造的な発想も必要になるかもしれません。 可能な限りにおいて、subtype_diff関数は、選択した演算子クラスと照合順が示唆するソート順と矛盾しないようにすべき、つまり、ソート順で、1番目の引数が2番目の引数より上に来る場合は、必ず差分関数の結果は正になるべきです。

範囲型の作成について、より詳細な情報はCREATE TYPEを参照してください。

8.17.9. インデックス

範囲型の列にGiSTおよびSP-GiSTインデックスを作成することができます。 例えば、GiSTインデックスを作成するには、

CREATE INDEX reservation_idx ON reservation USING gist (during);

GiSTあるいはSP-GiSTインデックスがあると、以下の範囲演算子を含む検索を高速に実行できます。 =、&&、<@、@>、<<、>>、-|-、&<、および&> (より詳細な情報は表9-44を参照してください)。

さらに、範囲型の列にB-treeおよびハッシュインデックスを作ることもできます。 これらのインデックスについては、基本的に、等値演算のみが有効な範囲の演算です。 範囲の値についてB-treeのソート順が、<および>演算子について定義されていますが、現実にはこの順序はあまり意味がなく、有効ではありません。 範囲型のB-treeとハッシュのサポートは実際にインデックスを作ることよりも、むしろ、検索時に内部的にソートやハッシュをできるようにするのが主な目的です。

8.17.10. 範囲の制約

UNIQUEはスカラー値には自然な制約ですが、範囲型には通常は適当ではありません。 代わりに排他(exclude)制約を使うことの方が適切なことが多いです(CREATE TABLE ... CONSTRAINT ... EXCLUDEを参照してください)。 排他制約により、範囲型について"重なりがない"などといった制約を指定することができます。 例えば、

CREATE TABLE reservation (
    during tsrange,
    EXCLUDE USING gist (during WITH &&)
);

この制約は、テーブル上で重なりのある値が同時に存在することを防ぎます。

INSERT INTO reservation VALUES
    ('[2010-01-01 11:30, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO reservation VALUES
    ('[2010-01-01 14:45, 2010-01-01 15:45)');
ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl"
DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts
with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).

btree_gistの拡張を使って通常のスカラーのデータ型について排他制約を定義することができます。 これをさらに範囲の排他と組み合わせることで大きな柔軟性を得ることができます。 例えば、btree_gistをインストールした時、次の制約は範囲の重なりについて、会議室の部屋番号も同じ時にのみ拒絶します。

CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING gist (room WITH =, during WITH &&)
);

INSERT INTO room_reservation VALUES
    ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO room_reservation VALUES
    ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');
ERROR:  conflicting key value violates exclusion constraint "room_reservation_room_during_excl"
DETAIL:  Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts
with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).

INSERT INTO room_reservation VALUES
    ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');
INSERT 0 1