こんにちわ 北川です。
JSON型に引き続き、PostgreSQL9.2で追加された範囲型を触ってみたいと思います。
http://www.postgresql.org/docs/9.2/static/rangetypes.html
http://www.postgresql.jp/document/9.2/html/rangetypes.html
範囲型
範囲型は、2つの値で範囲を表わすデータ型であり、
下記のように１つのカラムで、範囲を表すことができます。
|
1 2 3 4 |
SELECT times from weather_range ; times ----------------------------------------------- ["2013-04-01 16:00:00","2013-04-01 17:00:00") |
範囲型には、組み込みの範囲型が6つ用意されており、ほとんどの場合は
この組み込みの範囲型でなんとかなりそうです。
CREATE TYPE で独自の範囲型を作成することも可能です。
http://www.postgresql.jp/document/9.2/html/sql-createtype.html
- int4range integerの範囲
- int8range bigintの範囲
- numrange numericの範囲
- tsrange timestamp without time zoneの範囲
- tstzrange timestamp with time zoneの範囲
- daterange dateの範囲
取り敢えず、データの登録、検索を行いたいので、範囲型をカラムに持つテーブルを作成します。
今回は、tsrange を利用します。
|
1 2 3 4 5 6 7 8 |
demo=# CREATE TABLE weather_range (weather TEXT , times tsrange); CREATE TABLE demo=# \d weather_range Table "public.weather_range" Column | Type | Modifiers ---------+---------+----------- weather | text | times | tsrange | |
作成できました。引き続きデータの登録を行いたいと思いますが、
insert文を作成するにあたり境界の表現方法を知る必要があります。
境界の表現とは、下限/上限値を 含める/含めない の表現方法となり、
値を含める場合は、[ or ] で表現し、値を含めな場合は、( or ) 表現します。
文章であまりうまく表現できないので取り敢えず使ってみます。
下記のSQLの場合は, 2013-04-01 15:00 以上 2013-04-01 16:00 未満 を表します。
|
1 |
INSERT INTO weather_range VALUES ('晴れ', '[2013-04-01 15:00, 2013-04-01 16:00)'); |
いくつかテストデータを登録します。
|
1 2 3 |
INSERT INTO weather_range VALUES ('曇り', '[2013-04-01 16:00, 2013-04-01 17:00)'); INSERT INTO weather_range VALUES ('曇り', '[2013-04-02 15:00, 2013-04-02 16:00)'); INSERT INTO weather_range VALUES ('雨', '[2013-04-02 16:00, 2013-04-02 17:00)'); |
範囲型に対して利用可能な演算子が準備されていますので、それらを使ってデータの検索をしてみます。
http://www.postgresql.jp/document/9.2/html/functions-range.html#RANGE-OPERATORS-TABLE
|
1 2 3 4 5 6 7 8 9 10 11 12 |
demo=# SELECT * from weather_range where times @> '2013-04-01 16:00'::timestamp; weather | times ---------+----------------------------------------------- 曇り | ["2013-04-01 16:00:00","2013-04-01 17:00:00") (1 row) demo=# SELECT * from weather_range where times << tsrange('2013-04-01 20:00:00','2013-04-01 24:00:00'); weather | times ---------+----------------------------------------------- 晴れ | ["2013-04-01 15:00:00","2013-04-01 16:00:00") 曇り | ["2013-04-01 16:00:00","2013-04-01 17:00:00") (2 rows) |
インデックスの利用
範囲型では、GiSTインデックスを利用します。
インデックスにより、範囲演算子を利用した場合のパフォーマンスの向上が期待出来ます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
demo=# explain SELECT * from weather_range where times && tsrange('2013-04-01 15:00:00','2013-04-01 16:00:00'); QUERY PLAN ---------------------------------------------------------------------------------------- Index Scan using weather_range_idx on weather_range (cost=0.00..2.27 rows=1 width=38) Index Cond: (times && '["2013-04-01 15:00:00","2013-04-01 16:00:00")'::tsrange) (2 rows) demo=# CREATE INDEX weather_range_idx ON weather_range USING gist (times); CREATE INDEX demo=# explain SELECT * from weather_range where times && tsrange('2013-04-01 15:00:00','2013-04-01 16:00:00'); QUERY PLAN ------------------------------------------------------------------------------- Seq Scan on weather_range (cost=0.00..3.76 rows=1 width=38) Filter: (times && '["2013-04-01 15:00:00","2013-04-01 16:00:00")'::tsrange) (2 rows) |
範囲の制御
範囲型のメリットとして、重なりが無いことをDBで制御することが可能となります。
そのような制御は、排他制約を利用することによって可能となります。
weather_rangeテーブルに排他制約を追加します。排他制約はインデックスがあることが前提であるため
合わせてインデックスも追加する必要があります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
ALTER TABLE weather_range ADD EXCLUDE USING gist (times WITH &&); demo=# ALTER TABLE weather_range ADD EXCLUDE USING gist (times WITH &&); NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "weather_range_times_excl" for table "weather_range" ALTER TABLE demo=# \d weather_range Table "public.weather_range" Column | Type | Modifiers ---------+---------+----------- weather | text | times | tsrange | Indexes: "weather_range_idx" gist (times) "weather_range_times_excl" EXCLUDE USING gist (times WITH &&) |
排他制約を追加したテーブルに重なりがあるようにデータを追加すると
制約により追加できない事が確認できます。
|
1 2 3 |
demo=# INSERT INTO weather_range VALUES ('晴れ', '[2013-04-01 15:00, 2013-04-01 16:00)'); ERROR: conflicting key value violates exclusion constraint "weather_range_times_excl" DETAIL: Key (times)=(["2013-04-01 15:00:00","2013-04-01 16:00:00")) conflicts with existing key (times)=(["2013-04-01 15:00:00","2013-04-01 16:00:00")). |
まとめ
今までは、２つのカラムを利用して範囲を表現する必要があったのが、範囲型の登場で１つのカラムで
表す事ができます。また、インデックスによるパフォーマンス向上や排他制約によるデータの整合性の確保
などの恩恵を得られそうです。
