Postgres 9.2 新特性之：範圍型別 (Range Types)

PostgreSQL 9.2 的一項新特性就是範圍型別 range types，通過這個名字你可以輕鬆猜出該型別的用途，它可讓你為某列資料定義數值範圍。

這個簡單的特性可以讓我們不需要定義兩個欄位來描述數值的開始值和結束值，一個最直觀的例子就是：

1	postgres# CREATE TABLE salary_grid (id int, position_name text, start_salary int, end_salary int);

2	CREATE TABLE

3	postgres# INSERT INTO salary_grid VALUES (1, 'junior developper', 20000, 30000);

4	INSERT 0 1

5	postgres# INSERT INTO salary_grid VALUES (2, 'senior developper', 28000, 35000);

6	INSERT 0 1

7	postgres# INSERT INTO salary_grid VALUES (3, 'postgres developper', 50000, 70000);

8	INSERT 0 1

這個簡單的關係用於儲存一個給定的職位和待遇的範圍（你還需要確定工資的貨幣單位），重要的是你必須實現一些系統函式或者外部程式 API 來執行例如範圍的交叉或者聯合。

Postgres 9.2 允許你的應用直接在資料庫端實現範圍值，範圍型別包括：

• 4 位整數範圍, int4range
• 8 位整數範圍, int8range
• 數值範圍, numrange
• 無時區的時間戳範圍, tsrange
• 帶時間戳的時間範圍, tstzrange
• 日期範圍, daterange

你也可以定義自己的範圍型別，Postgre 官網文件給出了 float 的示例：

1	postgres# CREATE TYPE floatrange AS RANGE (

2	postgres# subtype = float8,

3	postgres# subtype_diff = float8mi);

有了這樣一個功能，我們前面提到的工資表格例子就可以改為：

01	postgres=# create table salary_grid (id int, position_name text, salary_range int4range);

02	CREATE TABLE

03	postgres=# INSERT INTO salary_grid VALUES (1, 'junior developper', '[20000, 30000]');

04	INSERT 0 1

05	postgres=# INSERT INTO salary_grid VALUES (2, 'senior developper', '[28000, 35000]');

06	INSERT 0 1

07	postgres=# INSERT INTO salary_grid VALUES (3, 'postgres developper', '[50000, 70000]');

08	INSERT 0 1

09	postgres=# select * from salary_grid;

10	id | position_name | salary_range

11	----+---------------------+---------------

12	1 | junior developper | [20000,30001)

13	2 | senior developper | [28000,35001)

14	3 | postgres developper | [50000,70001)

15

(3 rows)

很重要的一點是，如果使用的是括號()，元組資料的上界是排除在外的，而中括號[]則上界包含其中。

資料庫本身也包含不同的用於處理範圍型別的函式。

你可直接獲取一個給定範圍的最低和最高值：

1	postgres=# SELECT upper(salary_range), lower(salary_range) FROM salary_grid;

2	upper | lower

3	-------+-------

4	30001 | 20000

5	35001 | 28000

6	70001 | 50000

7

(3 rows)

你可以檢查某個值是否包含在給定範圍內：

1	postgres=# SELECT salary_range @> 4000 as check

2	postgres=# FROM salary_grid

3	postgres=# WHERE position_name = 'junior developper';

4

check

5

-------

6

f

7

(1 row)

這裡顯示 4000 並不包含在初級職位的待遇裡 [20000,30000].

這裡稍微複雜了一些，你還可以檢查兩個範圍之間的重疊的部分，這裡的 salary_range 使用的是 int4，因此 int4range 函式可用於此操作：

01	postgres=# WITH junior_salary AS (

02	SELECT salary_range as junior

03	FROM salary_grid

04	WHERE position_name = 'junior developper'),

05	senior_salary AS (

06	SELECT salary_range as senior

07	FROM salary_grid

08	WHERE position_name = 'senior developper')

09	SELECT int4range(junior) && int4range(senior) as check

10	FROM junior_salary, senior_salary;

11

check

12

-------

13

t

14

(1 row)

這裡顯示的是初級和高階職位之間的工資重疊部分。

你還可以設定無上下限的範圍型別，或者是隻有上限或者下限的範圍型別，讓我們來看一個非常現實的例子：

1	postgres# UPDATE salary_grid SET salary_range = '[50000,)' WHERE position_name = 'postgres developper';

2	UPDATE 0 1

3	postgres=# SELECT salary_range @> 60000000 as check

4	postgres-# FROM salary_grid WHERE position_name = 'postgres developper';

5

check

6

-------

7

t

8

(1 row)

你可以使用 lower_inf 或者 upper_inf 來檢查範圍的無限值。

Postgres 還有其他一些內嵌的函式（如 isempty），這個可以直接從官方文件中獲取詳細資訊。

你還可以閱讀《PostgreSQL 的陣列》