Postgresql 的 Range 資料型態

有了 Range 資料型態,人生變彩色的.

首先來看一個常見的 table 設計

create emp_schedule (
  id integer generated always as identity primary key
, emp_id integer references emp(id)
, start_time timestamptz
, end_time timestamptz
);

一般會使用類似 now() between start_time and end_time
或是比較偏數學風格的 start_time <= now() <= end_time 
這樣方式來查.但是較為複雜的查詢,往往會讓 SQL Command 不好下,
而且很容易在邊界判斷錯誤,思考也不直觀.因為這牽扯到兩個欄位,至少
三個變數.尤其是有 overlap 的情況下,那時常造成開發的瓶頸,驗證也
很困難.

Postgresql 引進了 range type 的概念,包含了幾種型態.
int4range, int8range, numrange, tsrange, tstzrange, daterange 

為方便討論,先以 daterange, tstzrange 為例.

 -Infinity |...| 2018-09-24 |         range 表示式           | 2019-09-05|...| Infinity 
---------------+------------+-------------------------------+-----------+---------------
<---------------------------------------- [,) ----------------------------------------->
---------------+------------+-------------------------------+-----------+---------------
                            | <-(2018,09,24, 2019-10-05) -> |
---------------+------------+-------------------------------+-----------+---------------
               |<---------------[2018,09,24, 2019-10-05) -> |
---------------+------------+-------------------------------+-----------+---------------
               |<---------------[2018,09,24, 2019-10-05] -------------> |
---------------+------------+-------------------------------+-----------+---------------
                            | <-(2018,09,24, 2019-10-05] -------------> |
---------------+------------+-------------------------------+-----------+---------------

在表示式中,( 代表不含邊界值, ] 代表含邊界值.
特殊字 Infinity 代表永恆,而且有正負之分. [current_date, Infinity) 這樣任何包含今天及以後的,除了
時光盡頭,都包含在在此範圍內,這樣可讀性高,容易理解,也易於系統更新.
若是使用某個資料型態的極大值,這樣早晚出問題.而且極難抓,也不易於理解.
但是 [,) 代表意義呢? 是否能夠包含永恆.

來看以下使用 包含於運算子 <@ 來做的運算.

select '[-infinity, infinity)' ::daterange <@ '[,)'::daterange
     , '[-infinity, infinity]' ::daterange <@ '[,]'::daterange
     , '[,)'::daterange <@ '[,]'::daterange;

+----------+----------+----------+
| ?column? | ?column? | ?column? |
+----------+----------+----------+
| t        | t        | t        |
+----------+----------+----------+

特殊字 Infinity 是用來讓我們易於理解.
故會得到上面的比較結果.
但虛無是否包含永恆? 另外有特殊字 empty.來看以下運算結果.

select '[-infinity, infinity)'::daterange @> 'empty'::daterange 
     , '[,)'::daterange @> 'empty'::daterange
     , '[2019-10-05, 2019-10-06)'::daterange @> 'empty'::daterange;

+----------+----------+----------+
| ?column? | ?column? | ?column? |
+----------+----------+----------+
| t        | t        | t        |
+----------+----------+----------+

注意: '[,)' 不是 empty.

select isempty('empty'::daterange)
     , isempty('[,)'::daterange);

+---------+---------+
| isempty | isempty |
+---------+---------+
| t       | f       |
+---------+---------+


接著來看怎樣輸入 range type, 除了上面有看到轉型法,還有構造函數法.

就是直接使用 range type 的名字. 例如以下方式

select daterange(2019-10-05, 2019-10-06, '[)');

default 是 [), default 可以不輸入. 左邊界需小於等於右邊界.

select daterange('2019-10-05', '2019-10-06') as "from_function"
     , daterange('2019-10-05', '2019-10-06') = '[2019-10-05, 2019-10-06)'::daterange
     , '[2019-10-05, 2019-10-06)'::daterange as "from_typecast";

+-------------------------+----------+-------------------------+
|      from_function      | ?column? |      from_typecast      |
+-------------------------+----------+-------------------------+
| [2019-10-05,2019-10-06) | t        | [2019-10-05,2019-10-06) |
+-------------------------+----------+-------------------------+

range type 提供了不少運算子,除了上面已經出現的 @> <@ , 還有其他運算子.

select oprleft::regtype
     , oprname
     , oprright::regtype
     , oprresult::regtype
     , oprcode::regproc
  from pg_catalog.pg_operator
 where oprleft::regtype = 'anyrange'::regtype
    or oprright::regtype = 'anyrange'::regtype
    or oprresult::regtype = 'anyrange'::regtype
 order by oprleft, oprname, oprright, oprresult;
 
+------------+---------+------------+-----------+-------------------------+
|  oprleft   | oprname |  oprright  | oprresult |         oprcode         |
+------------+---------+------------+-----------+-------------------------+
| anyelement | <@      | anyrange   | boolean   | elem_contained_by_range |
| anyrange   | &&      | anyrange   | boolean   | range_overlaps          |
| anyrange   | &<      | anyrange   | boolean   | range_overleft          |
| anyrange   | &>      | anyrange   | boolean   | range_overright         |
| anyrange   | *       | anyrange   | anyrange  | range_intersect         |
| anyrange   | +       | anyrange   | anyrange  | range_union             |
| anyrange   | -       | anyrange   | anyrange  | range_minus             |
| anyrange   | -|-     | anyrange   | boolean   | range_adjacent          |
| anyrange   | <       | anyrange   | boolean   | range_lt                |
| anyrange   | <<      | anyrange   | boolean   | range_before            |
| anyrange   | <=      | anyrange   | boolean   | range_le                |
| anyrange   | <>      | anyrange   | boolean   | range_ne                |
| anyrange   | <@      | anyrange   | boolean   | range_contained_by      |
| anyrange   | =       | anyrange   | boolean   | range_eq                |
| anyrange   | >       | anyrange   | boolean   | range_gt                |
| anyrange   | >=      | anyrange   | boolean   | range_ge                |
| anyrange   | >>      | anyrange   | boolean   | range_after             |
| anyrange   | @>      | anyelement | boolean   | range_contains_elem     |
| anyrange   | @>      | anyrange   | boolean   | range_contains          |
+------------+---------+------------+-----------+-------------------------+
(19 rows)

19種運算子, 裡面有兩個是 anyelement, 可以算17種, 一般常見的 = , >, <, >=, <= 還有
剛才的 <@ @> 應該都好理解.其餘的不易用文字描述.我們來看以下的圖示


 +----------+
 |    A     |
 +----------+
         +------------+
         |     B      |
         +------------+

    |<------>|            intersection A*B
 |<------------------>|   union A+B
 |<----->|                difference A-B
             |<------>|   difference B-A
                          A && B = True (overlaps)

以上是有 overlaps 的情況

+----------+   +-------+
|    A     |   |   B   |  A << B = True
+----------+   +-------+

+----------++-------+
|    A     ||   B   |  A -|- B = True
+----------++-------+


函數部分,lower_inf(), upper_inf(), 搭配 Infinity 的情況.

select upper_inf('(,)'::daterange)
     , upper_inf('(,Infinity)'::daterange)
     , upper_inf('(,]'::daterange)
     , upper_inf('(,Infinity]'::daterange)
     ;
+-----------+-----------+-----------+-----------+
| upper_inf | upper_inf | upper_inf | upper_inf |
+-----------+-----------+-----------+-----------+
| t         | f         | t         | f         |
+-----------+-----------+-----------+-----------+

select lower_inf('(,)'::daterange)
     , lower_inf('(-Infinity,)'::daterange)
     , lower_inf('(,]'::daterange)
     , lower_inf('(-Infinity,]'::daterange);

+-----------+-----------+-----------+-----------+
| lower_inf | lower_inf | lower_inf | lower_inf |
+-----------+-----------+-----------+-----------+
| t         | f         | t         | f         |
+-----------+-----------+-----------+-----------+

沒有值的就是 True, 即使是 Infinity,也是屬於有值.

lower_inc(), upper_inc(), 是用來判斷 ( ].

select lower_inc(numrange(1.1,2.2, '()'))
     , lower_inc(numrange(1.1,2.2, '[)'))
     , upper_inc(numrange(1.1,2.2, '()'))
     , upper_inc(numrange(1.1,2.2, '(]'));

+-----------+-----------+-----------+-----------+
| lower_inc | lower_inc | upper_inc | upper_inc |
+-----------+-----------+-----------+-----------+
| f         | t         | f         | t         |
+-----------+-----------+-----------+-----------+

range_merge() 跟 union operator (+) 不同點在於 不需要重疊(overlaps)

select range_merge('[1,2)'::int4range, '(5,6]'::int4range);

+-------------+
| range_merge |
+-------------+
| [1,7)       |
+-------------+

注意到 最終還是 [) 型態.

range types 是非常強大的資料型態.今天先就基本的概念做一介紹.
後面再搭配其他項目,會有更多應用可以看到.