範圍類型是代表某種元素類型(稱為範圍的子類型)的值範圍的資料類型。例如,timestamp
的範圍可以用於表示會議室被預訂的時間範圍。在這種情況下,資料類型是 tsrange
(「timestamp range」 的簡稱),而 timestamp
是子類型。子類型必須具有總排序,以便明確定義元素值是否在值範圍之內、之前或之後。
範圍類型很有用,因為它們在單個範圍值中表示多個元素值,並且可以清楚地表達重疊範圍等概念。將時間和日期範圍用於排程目的是最清楚的範例;但是,價格範圍、儀器的測量範圍等等也可能很有用。
每個範圍類型都有一個對應的多重範圍類型。多重範圍是一個非連續、非空、非空的範圍的排序列表。大多數範圍運算符也適用於多重範圍,並且它們有自己的一些函數。
PostgreSQL 附帶以下內建範圍類型
int4range
— integer
的範圍,int4multirange
— 對應的多重範圍
int8range
— bigint
的範圍,int8multirange
— 對應的多重範圍
numrange
— numeric
的範圍,nummultirange
— 對應的多重範圍
tsrange
— timestamp without time zone
的範圍,tsmultirange
— 對應的多重範圍
tstzrange
— timestamp with time zone
的範圍,tstzmultirange
— 對應的多重範圍
daterange
— date
的範圍,datemultirange
— 對應的多重範圍
此外,您可以定義自己的範圍類型;有關更多資訊,請參閱 CREATE TYPE。
CREATE TABLE reservation (room int, during tsrange); INSERT INTO reservation VALUES (1108, '[2010-01-01 14:30, 2010-01-01 15:30)'); -- Containment SELECT int4range(10, 20) @> 3; -- Overlaps SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0); -- Extract the upper bound SELECT upper(int8range(15, 25)); -- Compute the intersection SELECT int4range(10, 20) * int4range(15, 25); -- Is the range empty? SELECT isempty(numrange(1, 5));
每個非空範圍都有兩個邊界,下限和上限。這些值之間的所有點都包含在範圍中。包含性邊界表示邊界點本身也包含在範圍中,而排除性邊界表示邊界點不包含在範圍中。
在範圍的文字形式中,包含性下限由 「[
」 表示,而排除性下限由 「(
」 表示。同樣地,包含性上限由 「]
」 表示,而排除性上限由 「)
」 表示。(有關更多詳細資訊,請參閱第 8.17.5 節。)
函數 lower_inc
和 upper_inc
分別測試範圍值的下限和上限的包含性。
範圍的下限可以省略,這表示範圍中包含小於上限的所有值,例如 (,3]
。同樣地,如果範圍的上限被省略,則範圍中包含大於下限的所有值。如果下限和上限都被省略,則元素類型的所有值都被認為在範圍內。將遺失的邊界指定為包含性會自動轉換為排除性,例如,[,]
會轉換為 (,)
。您可以將這些遺失的值視為 +/- 無限大,但它們是特殊的範圍類型值,並且被認為超出任何範圍元素類型的 +/- 無限大值。
具有「無限大」概念的元素類型可以使用它們作為明確的邊界值。 例如,對於時間戳記範圍,[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
可以是子類型的有效輸入字串,或為空以表示沒有下限。 同樣地,upper-bound
可以是子類型的有效輸入字串,或為空以表示沒有上限。
每個邊界值都可以使用 "
(雙引號) 字元來引起來。 如果邊界值包含括號、方括號、逗號、雙引號或反斜線,則這是必要的,因為這些字元否則會被視為範圍語法的一部分。 若要在引用的邊界值中放入雙引號或反斜線,請在其前面加上反斜線。 (此外,雙引號引起來的邊界值中的一對雙引號被視為表示雙引號字元,類似於 SQL 文字字串中單引號的規則。)或者,您可以避免引用,並使用反斜線跳脫字元來保護所有否則會被視為範圍語法的資料字元。 此外,要寫入作為空字串的邊界值,請寫入 ""
,因為什麼都不寫表示無限邊界。
範圍值之前和之後允許有空白,但括號或方括號之間的任何空白都會被視為下限或上限值的一部分。(根據元素類型,它可能重要也可能不重要。)
這些規則與在複合類型文字中寫入欄位值的規則非常相似。 有關其他說明,請參閱 第 8.16.6 節。
範例
-- includes 3, does not include 7, and does include all points in between SELECT '[3,7)'::int4range; -- does not include either 3 or 7, but includes all points in between SELECT '(3,7)'::int4range; -- includes only the single point 4 SELECT '[4,4]'::int4range; -- includes no points (and will be normalized to 'empty') SELECT '[4,4)'::int4range;
multirange 的輸入是花括號 ({
和 }
),其中包含零個或多個有效的範圍,以逗號分隔。 括號和逗號周圍允許有空白。 這旨在讓人聯想到陣列語法,儘管 multirange 簡單得多:它們只有一個維度,並且無需引用其內容。(但是,它們範圍的邊界可以如上所述引用。)
範例
SELECT '{}'::int4multirange; SELECT '{[3,7)}'::int4multirange; SELECT '{[3,7), [8,9)}'::int4multirange;
每個範圍類型都有一個建構函式,其名稱與範圍類型相同。 使用建構函式通常比編寫範圍文字常數更方便,因為它可以避免對邊界值進行額外引用的需要。 建構函式接受兩個或三個引數。 雙引數形式以標準形式建構範圍(包含下限,排除上限),而三引數形式建構具有由第三個引數指定的邊界形式的範圍。 第三個引數必須是字串 「()
」、「(]
」、「[)
」 或 「[]
」 之一。 例如
-- The full form is: lower bound, upper bound, and text argument indicating -- inclusivity/exclusivity of bounds. SELECT numrange(1.0, 14.0, '(]'); -- If the third argument is omitted, '[)' is assumed. SELECT numrange(1.0, 14.0); -- Although '(]' is specified here, on display the value will be converted to -- canonical form, since int8range is a discrete range type (see below). SELECT int8range(1, 14, '(]'); -- Using NULL for either bound causes the range to be unbounded on that side. SELECT numrange(NULL, 2.2);
每個範圍類型也都有一個 multirange 建構函式,其名稱與 multirange 類型相同。 建構函式採用零個或多個引數,這些引數都是適當類型的範圍。 例如
SELECT nummultirange(); SELECT nummultirange(numrange(1.0, 14.0)); SELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));
離散範圍是一種元素類型具有明確定義的「步長」的範圍,例如 integer
或 date
。 在這些類型中,可以說兩個元素是相鄰的,當它們之間沒有有效值時。 這與連續範圍形成對比,在連續範圍中,始終(或幾乎始終)可以識別兩個給定值之間的其他元素值。 例如,在 numeric
類型上的範圍是連續的,在 timestamp
上的範圍也是如此。(即使 timestamp
具有有限的精度,因此理論上可以將其視為離散的,但最好將其視為連續的,因為步長通常不重要。)
思考離散範圍類型的另一種方式是,對於每個元素值,都有一個明確的「下一個」或「前一個」值的概念。 了解這一點後,就可以通過選擇下一個或前一個元素值來代替最初給定的元素值,從而在範圍邊界的包含和排除表示之間進行轉換。 例如,在整數範圍類型中,[4,8]
和 (3,9)
表示相同的數值集; 但對於數字上的範圍而言,情況並非如此。
離散範圍類型應具有一個正規化函式,該函式知道元素類型的所需步長。 正規化函式負責將範圍類型的等效值轉換為具有相同的表示形式,尤其是在包含或排除邊界方面始終如一。 如果未指定正規化函式,則具有不同格式的範圍將始終被視為不相等,即使它們實際上可能表示相同的值集。
內建範圍類型 int4range
、int8range
和 daterange
都使用包含下限並排除上限的規範形式;也就是說,[)
。 但是,使用者定義的範圍類型可以使用其他慣例。
使用者可以定義自己的範圍類型。 這樣做的最常見原因是使用內建範圍類型中未提供的子類型的範圍。 例如,要定義一個 float8
子類型的新範圍類型
CREATE TYPE floatrange AS RANGE ( subtype = float8, subtype_diff = float8mi ); SELECT '[1.234, 5.678]'::floatrange;
因為 float8
沒有有意義的「步長」,所以在本範例中我們不定義正規化函式。
當您定義自己的範圍時,您會自動獲得相應的 multirange 類型。
定義您自己的範圍類型還允許您指定不同的子類型 B-tree 運算符類別或排序規則來使用,以便更改確定哪些值落入給定範圍的排序。
如果子類型被視為具有離散而非連續的值,則 CREATE TYPE
指令應指定一個 canonical
函數。這個標準化函數會接收一個輸入範圍值,並且必須傳回一個等效的範圍值,其邊界和格式可能不同。對於代表相同值集合的兩個範圍,例如整數範圍 [1, 7]
和 [1, 8)
,標準化的輸出必須相同。您選擇哪個表示法作為標準化形式並不重要,只要兩個具有不同格式的等效值總是映射到具有相同格式的相同值即可。除了調整包含/排除邊界的格式外,標準化函數還可以將邊界值四捨五入,以防所需的步長大於子類型能夠儲存的範圍。例如,可以定義一個基於 timestamp
的範圍類型,其步長為一小時,在這種情況下,標準化函數需要將非整數小時的邊界四捨五入,或者乾脆拋出錯誤。
此外,任何旨在與 GiST 或 SP-GiST 索引一起使用的範圍類型都應定義一個子類型差異(subtype difference)或 subtype_diff
函數。(即使沒有 subtype_diff
,索引仍然可以運作,但效率可能會遠低於提供差異函數的情況。)子類型差異函數會接收兩個子類型的輸入值,並傳回它們的差異(即 X
減去 Y
),以 float8
值表示。在上面的例子中,可以使用作為常規 float8
減號運算符基礎的 float8mi
函數;但對於任何其他子類型,都需要進行一些類型轉換。可能還需要針對如何將差異表示為數字進行一些創造性的思考。在最大程度上,subtype_diff
函數應與所選運算符類別和定序 (collation) 所暗示的排序順序一致;也就是說,如果第一個參數根據排序順序大於第二個參數,則其結果應為正值。
一個不那麼過於簡化的 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;
有關創建範圍類型的更多資訊,請參閱 CREATE TYPE。
可以為範圍類型的表欄位建立 GiST 和 SP-GiST 索引。也可以為多重範圍類型的表欄位建立 GiST 索引。例如,要建立一個 GiST 索引:
CREATE INDEX reservation_idx ON reservation USING GIST (during);
範圍上的 GiST 或 SP-GiST 索引可以加速涉及以下範圍運算符的查詢:=
、&&
、<@
、@>
、<<
、>>
、-|-
、&<
和 &>
。多重範圍上的 GiST 索引可以加速涉及相同多重範圍運算符集的查詢。範圍上的 GiST 索引和多重範圍上的 GiST 索引也可以分別加速涉及以下交叉類型範圍到多重範圍和多重範圍到範圍運算符的查詢:&&
、<@
、@>
、<<
、>>
、-|-
、&<
和 &>
。有關更多資訊,請參閱 表 9.56。
此外,可以為範圍類型的表欄位建立 B-tree 和雜湊索引。對於這些索引類型,基本上唯一有用的範圍操作是相等性。為範圍值定義了 B-tree 排序,帶有相應的 <
和 >
運算符,但排序相當隨意,在現實世界中通常沒有用處。範圍類型的 B-tree 和雜湊支援主要用於允許查詢中的內部排序和雜湊,而不是建立實際的索引。
雖然 UNIQUE
是標量值的自然約束,但它通常不適用於範圍類型。相反,排除約束通常更合適(請參閱 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
如果您在文檔中看到任何不正確、與特定功能的體驗不符或需要進一步澄清的內容,請使用此表單來報告文檔問題。