8.17. 范圍類型 · PostgreSQL 中文文檔 9.3

# 8.17\. 范圍類型范圍數據類型代表著某一元素類型在一定范圍內的值。 (此元素類型稱為該范圍的 _子類型_)。例如，`timestamp` 范圍可能被用于代表一間會議室被預定的時間范圍。這種情況下數據類型為`tsrange` ("timestamp range"的簡寫)，并且`timestamp` 是子類型。子類型必須具備完整的排序，這樣清晰定義了元素值在范圍之內，之前，或者之后。范圍類型是有用的。因為他們代表了在單一范圍內的許多元素值，并且清晰表達了諸如重疊范圍等概念。出于計劃目的的時間和日期范圍的使用是一個最清晰的例子；價格范圍，儀器測量的范圍等也有用。 ## 8.17.1\. 內嵌范圍類型 PostgreSQL 提供下列內嵌范圍類型： * `int4range` — `integer`的范圍 * `int8range` —`bigint`的范圍 * `numrange` —`numeric`的范圍 * `tsrange` —`timestamp without time zone`的范圍 * `tstzrange` —`timestamp with time zone`的范圍 * `daterange` —`date`的范圍此外，你可以定義你自己的范圍類型；更多信息見[CREATE TYPE](#calibre_link-100)。 ## 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)); ``` 范圍類型的操作數和函數的完整列表見[Table 9-44](#calibre_link-1983) 及[Table 9-45](#calibre_link-1984)。 ## 8.17.3\. 包含及不包含邊界每個非空范圍有兩個邊界，下邊界和上邊界。這兩個值之間的所有點都包含在范圍內。包含邊界意味著邊界點本身包含在范圍內，而不包含邊界意味著邊界點不包含在范圍內。在一個文本格式的范圍里，包含下邊界由"`[`"代表，而不包含下邊界由 "`(`"代表。同樣，一個包含上邊界由 "`]`"代表，而不包含上邊界由"`)`" 代表。 (更多細節見[Section 8.17.5](#calibre_link-1985)）函數`lower_inc`和`upper_inc`分別檢測一個范圍值的上下邊界是否包含。 ## 8.17.4\. 無限（無邊界）范圍一個范圍的下邊界可以被省略，意味著小于上邊界的所有點都包含在范圍內。同樣，如果范圍的上邊界被省略，那么所有大于下邊界的點都包含在范圍內。如果上下邊界都被省略，那么所有元素類型的值都被認為在范圍內。相當于分別認為下邊界是"負無窮大"，或者上邊界是"正無窮大"。但是注意這些無窮大值絕不是范圍元素類型的值，而且絕不是范圍的一部分。（所以沒有包含無窮邊界之類的東西 — 如果你嘗試寫一個，它會被自動轉換成一個不包含邊界。）某些元素類型還有一個"無限"的概念，但是只要涉及到范圍類型機制它就只是另一個值。例如，在timestamp范圍里，`[today,]`意味著和`[today,)`是相同的東西。但是 `[today,infinity]`意味著與`[today,infinity)` 不同的東西 — 后者不包含特殊的`timestamp`值`infinity`。函數`lower_inf`和`upper_inf`分別檢測一個范圍的無限下邊界和上邊界。 ## 8.17.5\. 范圍輸入/輸出范圍值的輸入必須遵循下面的格式： ``` (_下邊界_,_上邊界_) (_下邊界_,_上邊界_] [_下邊界_,_上邊界_) [_下邊界_,_上邊界_] 空 ``` 如前所述，圓括號或者方括號顯示下邊界和上邊界是不包含的還是包含的。注意最后的格式是 `空`，代表著一個空的范圍（一個不含有值的范圍）。 `_下邊界_`可以是子類型有效輸入的一個字符串，或者是空以顯示沒有下邊界。同樣，`_上邊界_`可以是子類型有效輸入的一個字符串，或者是空以顯示沒有上邊界。每個邊界值可以用`"`（雙引號）字符引用。如果邊界值包含圓括號，方括號，逗號，雙引號，或者反斜杠，這就很有必要。因為不這樣的話，這些字符會被當成范圍語法的一部分。要想把雙引號或反斜杠放入一個引用的邊界值，就在它前面加一個反斜杠。（另外，加了雙引號的邊界值內的兩個連續的雙引號用來表示一個雙引號字符，類似于SQL文字串內的單引號規則。）或者，你可以避免用引號，使用反斜杠轉義來保護所有數據字符不被認為是范圍語法。而且，要寫入一個空字符串邊界值，用`""` 。這是因為什么也不寫入意味著無限邊界。在范圍值前后可以有空格，但是在圓括號和方括號之間的任何空格都被認為是上邊界或下邊界的一部分。（重要還是不重要取決于元素類型。） > **Note:** 這些規則很類似于在復合類型常量中寫入字段值。更多注釋見[Section 8.16.5](#calibre_link-1986)。例子： ``` -- 包括3，不包括7，并且包括二者之間的所有點 SELECT '[3,7)'::int4range; -- 不包括3和7，但是包括二者之間所有點 SELECT '(3,7)'::int4range; -- 只包括單一值4 SELECT '[4,4]'::int4range; -- 不包括點（被標準化為‘空’） SELECT '[4,4)'::int4range; ``` ## 8.17.6\. 構造范圍每個范圍有一個與范圍類型同名的構造函數。使用構造函數往往比寫入一個范圍文本常量更便利，因為它避免了額外引用邊界值的需要。構造函數接受兩到三個參數。兩參數方式構造一個標準格式的范圍（包含下邊界，不包含上邊界），而三參數方式用第三個參數指定邊界來構造范圍。第三個參數必須是下面的字符串之一 "`()`", "`(]`", "`[)`", 或 "`[]`". 例如： ``` -- 完整方式為：下邊界，上邊界，和指示包含還是不包含邊界的文本參數 SELECT numrange(1.0, 14.0, '(]'); -- 如果第三個參數省略，使用‘[)’。 SELECT numrange(1.0, 14.0); -- 盡管在這里指定了‘(]’，然而該值會被轉換成標準格式。這是由于int8range是一個離散范圍類型（見下面）。 SELECT int8range(1, 14, '(]'); -- 使用NULL作任一邊界會導致范圍在那一邊沒有邊界。 SELECT numrange(NULL, 2.2); ``` ## 8.17.7\. 離散范圍類型離散范圍的元素類型有一個完善定義的"階梯"，例如`integer`或者 `date`。當在這些類型里兩個元素中間沒有有效值時，它們可被稱為是鄰近的。與之形成對比的是連續范圍，總是（或者幾乎總是）可能在兩個給定值之間找到其它元素。例如， `numeric`類型的范圍是連續的，和`timestamp`一樣。（即使`timestamp`精度有限，理論上可以被當做是離散的，但是既然不關心階梯大小最好還是把它當做是連續的。) 考慮一個離散范圍的另一種方式是每一個元素值都清晰知道它的"下一個"或者"上一個" 值。知道了這個，通過選擇下一個或者上一個元素值而不是開始給定的值，就可能在一個范圍邊界的包含和不包含表達之間進行轉換。例如，在一個整數范圍類型里`[4,8]`和`(3,9)` 提供相同的值集合；但是數值范圍不是這樣。離散范圍應當有一個_標準化_函數，該函數知道元素類型想要的階梯大小。這個標準化函數負責把該范圍類型的等效值轉換成同一表達方式，尤其是包含或不包含邊界。如果不指定一個標準化函數，不同格式的范圍會被認為是不相等的，即使它們實際上可能代表相同的值集合。內嵌范圍類型`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`范圍類型可以被定義為以一個小時作為階梯值。這種情況下標準化函數需要把不是一小時的整數倍的值化成一小時的整數倍，或者可能拋出一個錯誤。定義你自己的范圍類型也允許你指定使用一個不同的子類型B-tree操作符類或排序規則，以便改變排序次序來決定哪些值落入一個給定的范圍。此外，任何打算要使用GiST或SP-GiST索引的范圍類型應當定義一個子類型差異，或者 `subtype_diff`函數。（沒有`subtype_diff`索引仍然可以起作用，但是比起提供差異函數時可能相當低效。）子類型差異函數采用子類型的兩個輸入值，并返回它們之間表示為 `float8`值的差異（就是說，`_X_`減去`_Y_`）。在我們上面的例子中，可以使用常規`float8`減操作符調用的函數；但是對其它子類型，類型轉換似乎是必要的。關于怎樣將差異表示為數字的某些創新想法可能也是必要的。 `subtype_diff`函數應當盡最大可能與所選操作符類和排序規則表明的排序次序相一致；即，每當根據排序次序第一個參數大于第二個參數時，結果應當是正數。更多創建范圍類型的信息見[CREATE TYPE](#calibre_link-100)。 ## 8.17.9\. 索引可以對范圍類型的表列創建GiST和SP-GiST索引。例如，要創建一個GiST索引： ``` CREATE INDEX reservation_idx ON reservation USING gist (during); ``` 一個GiST或者SP-GiST索引可以加速包含這些范圍操作符的查詢： `=`, `&&`, `<@`, `@>`, `<<`, `>>`, `-|-`, `&<`, and `&>` （更多信息見[Table 9-44](#calibre_link-1983)）。此外，對范圍類型的表列可以創建B-tree和哈希索引。對這些索引類型，基本上唯一可用的范圍操作是等于。用相應的`<`和`>`操作符可以為范圍索引定義一個B-tree排序次序，但是這個次序相當武斷，在現實世界中通常沒有用。范圍類型的B-tree和哈希支持主要是用于查詢內部的排序和哈希操作，而不是用于實際索引的創建。 ## 8.17.10\. 范圍上的約束當`UNIQUE`是一個對標量值的自然約束時，對范圍類型通常是不合適的。反而不包含約束往往更合適（見[CREATE TABLE ... CONSTRAINT ... EXCLUDE](#calibre_link-1064)）。不包含約束允許對一個范圍類型指定約束，比如 "非重疊"。例如： ``` 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`](#calibre_link-372)擴展對簡單標量數據類型定義不包含約束。簡單標量數據類型可以和范圍不包含結合來獲得最大的靈活性。例如，在`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 ```