PostgreSQL 范圍類型

PostgreSQL 帶有下列內建范圍類型：

此外，你可以定義自己的范圍類型，詳見CREATE TYPE。

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.53以及表 9.54。

每一個非空范圍都有兩個界限，下界和上界。這些值之間的所有點都被包括在范圍內。一個包含界限意味著邊界點本身也被包括在范圍內，而一個排除邊界意味著邊界點不被包括在范圍內。

在一個范圍的文本形式中，一個包含下界被表達為“[”而一個排除下界被表達為“(”。同樣，一個包含上界被表達為“]”而一個排除上界被表達為 “)”（詳見本文中第 8.17.5 節(jié)）。

函數lower_inc和upper_inc分別測試一個范圍值的上下界。

一個范圍的下界可以被忽略，意味著所有小于上界的值都被包括在范圍中，例如(,3]。 同樣，如果范圍的上界被忽略，那么所有比上界大的值都被包括在范圍中。如果上下界都被忽略，該元素類型的所有值都被認為在該范圍中。 規(guī)定缺失的包括界限自動轉換為排除，例如，[,] 轉換為 (,)。 你可以認為這些缺失值為 +/- 無窮大，但它們是特殊范圍類型值，并且被視為超出任何范圍元素類型的 +/- 無窮大值。

具有“infinity”概念的元素類型可以用它們作為顯式邊界值。 例如，在時間戳范圍，[today,infinity) 不包括特殊的 timestamp 值 infinity,盡管 [today,infinity] 包括它, 就好比 [today,) 和 [today,].

函數lower_inf和upper_inf分別測試一個范圍的無限上下界。

一個范圍值的輸入必須遵循下列模式之一：

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

圓括號或方括號指示上下界是否為排除的或者包含的。注意最后一個模式是empty，它表示一個空范圍（一個不包含點的范圍）。

lower-bound可以是作為 subtype 的合法輸入的一個字符串，或者是空表示沒有下界。同樣，upper-bound可以是作為 subtype 的合法輸入的一個字符串，或者是空表示沒有上界。

每個界限值可以使用"（雙引號）字符引用。如果界限值包含圓括號、方括號、逗號、雙引號或反斜線時，這樣做是必須的，因為否則那些字符會被認作范圍語法的一部分。要把一個雙引號或反斜線放在一個被引用的界限值中，就在它前面放一個反斜線（還有，在一個雙引號引用的界限值中的一對雙引號表示一個雙引號字符，這與 SQL 字符串中的單引號規(guī)則類似）。此外，你可以避免引用并且使用反斜線轉義來保護所有數據字符，否則它們會被當做返回語法的一部分。還有，要寫一個是空字符串的界限值，則可以寫成 ""，因為什么都不寫表示一個無限界限。

范圍值前后允許有空格，但是圓括號或方括號之間的任何空格會被當做上下界值的一部分（取決于元素類型，它可能是也可能不是有意義的）。

例子：

-- 包括 3，不包括 7，并且包括 3 和 7 之間的所有點
SELECT '[3,7)'::int4range;

-- 既不包括 3 也不包括 7，但是包括之間的所有點
SELECT '(3,7)'::int4range;

-- 只包括單獨一個點 4
SELECT '[4,4]'::int4range;

-- 不包括點（并且將被標準化為 '空'）
SELECT '[4,4)'::int4range;

每一種范圍類型都有一個與其同名的構造器函數。使用構造器函數常常比寫一個范圍文字常數更方便，因為它避免了對界限值的額外引用。構造器函數接受兩個或三個參數。兩個參數的形式以標準的形式構造一個范圍（下界是包含的，上界是排除的），而三個參數的形式按照第三個參數指定的界限形式構造一個范圍。第三個參數必須是下列字符串之一： “()”、 “(]”、 “[)”或者 “[]”。 例如：

-- 完整形式是：下界、上界以及指示界限包含性/排除性的文本參數。 SELECT numrange(1.0, 14.0, '(]'); -- 如果第三個參數被忽略，則假定為 '[)'。 SELECT numrange(1.0, 14.0); -- 盡管這里指定了 '(]'，顯示時該值將被轉換成標準形式，因為 int8range 是一種離散范圍類型（見下文）。 SELECT int8range(1, 14, '(]'); -- 為一個界限使用 NULL 導致范圍在那一邊是無界的。 SELECT numrange(NULL,
            2.2);

一種范圍的元素類型具有一個良定義的“步長”，例如integer或date。在這些類型中，如果兩個元素之間沒有合法值，它們可以被說成是相鄰。這與連續(xù)范圍相反，連續(xù)范圍中總是（或者幾乎總是）可以在兩個給定值之間標識其他元素值。例如，numeric類型之上的一個范圍就是連續(xù)的， timestamp上的范圍也是（盡管timestamp具有有限的精度，并且在理論上可以被當做離散的，最好認為它是連續(xù)的，因為通常并不關心它的步長）。

另一種考慮離散范圍類型的方法是對每一個元素值都有一種清晰的“下一個”或“上一個”值。了解了這種思想之后，通過選擇原來給定的下一個或上一個元素值來取代它，就可以在一個范圍界限的包含和排除表達之間轉換。例如，在一個整數范圍類型中，[4,8]和 (3,9)表示相同的值集合，但是對于 numeric 上的范圍就不是這樣。

一個離散范圍類型應該具有一個正規(guī)化函數，它知道元素類型期望的步長。正規(guī)化函數負責把范圍類型的相等值轉換成具有相同的表達，特別是與包含或者排除界限一致。如果沒有指定一個正規(guī)化函數，那么具有不同格式的范圍將總是會被當作不等，即使它們實際上是表達相同的一組值。

內建的范圍類型int4range、int8range和daterange都使用一種正規(guī)的形式，該形式包括下界并且排除上界，也就是[)。不過，用戶定義的范圍類型可以使用其他習慣。

用戶可以定義他們自己的范圍類型。這樣做最常見的原因是為了使用內建范圍類型中沒有提供的 subtype 上的范圍。例如，要創(chuàng)建一個 subtype float8的范圍類型：

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

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

因為float8沒有有意義的“步長”，我們在這個例子中沒有定義一個正規(guī)化函數。

定義自己的范圍類型也允許你指定使用一個不同的子類型 B-樹操作符類或者集合， 以便更改排序順序來決定哪些值會落入到給定的范圍中。

如果 subtype 被認為是具有離散值而不是連續(xù)值，CREATE TYPE命令應當指定一個canonical函數。正規(guī)化函數接收一個輸入的范圍值，并且必須返回一個可能具有不同界限和格式的等價的范圍值。對于兩個表示相同值集合的范圍（例如[1, 7]和[1, 8)），正規(guī)的輸出必須一樣。選擇哪一種表達作為正規(guī)的沒有關系，只要兩個具有不同格式的等價值總是能被映射到具有相同格式的相同值就行。除了調整包含/排除界限格式外，假使期望的補償比 subtype 能夠存儲的要大，一個正規(guī)化函數可能會舍入邊界值。例如，一個timestamp之上的范圍類型可能被定義為具有一個一小時的步長，這樣正規(guī)化函數可能需要對不是一小時的倍數的界限進行舍入，或者可能直接拋出一個錯誤。

另外，任何打算要和 GiST 或 SP-GiST 索引一起使用的范圍類型應當定一個 subtype 差異或subtype_diff函數（沒有subtype_diff時索引仍然能工作，但是可能效率不如提供了差異函數時高）。subtype 差異函數采用兩個 subtype 輸入值，并且返回表示為一個float8值的差（即X減 Y ）。在我們上面的例子中，可以使用常規(guī)float8減法操作符之下的函數。但是對于任何其他 subtype，可能需要某種類型轉換。還可能需要一些關于如何把差異表達為數字的創(chuàng)新型想法。為了最大的可擴展性，subtype_diff函數應該同意選中的操作符類和排序規(guī)則所蘊含的排序順序，也就是說，只要它的第一個參數根據排序順序大于第二個參數，它的結果就應該是正值。

subtype_diff函數的一個不那么過度簡化的例子：

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

CREATE TYPE timerange AS RANGE (
    subtype = time,
    subtype_diff = time_subtype_diff
);

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

更多關于創(chuàng)建范圍類型的信息請參考CREATE TYPE。

可以為范圍類型的表列創(chuàng)建 GiST 和 SP-GiST 索引。例如，要創(chuàng)建一個 GiST 索引：

CREATE INDEX reservation_idx ON reservation USING GIST (during);

一個 GiST 或 SP-GiST 索引可以加速涉及以下范圍操作符的查詢： =、 &&、 <@、 @>、 <<、 >>、 -|-、 &<以及 &> （詳見表 9.53）。

此外，B-樹和哈希索引可以在范圍類型的表列上創(chuàng)建。對于這些索引類型，基本上唯一有用的范圍操作就是等值。使用相應的< 和 >操作符，對于范圍值定義有一種 B-樹排序順序，但是該順序相當任意并且在真實世界中通常不怎么有用。范圍類型的 B-樹和哈希支持主要是為了允許在查詢內部進行排序和哈希，而不是創(chuàng)建真正的索引。

雖然UNIQUE是標量值的一種自然約束，它通常不適合于范圍類型。反而，一種排除約束常常更加適合（見CREATE TABLE ... CONSTRAINT ... EXCLUDE）。排除約束允許在一個范圍類型上說明諸如“non-overlapping”的約束。例如：

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