PostgreSQLの範囲型と排他制約

Transcript

1. PostgreSQLの 範囲型と排他制約 PostgreSQL勉強会@札幌 2013.12.17 @iakio
2. 範囲型とは • 範囲をあらわすデータ型（そのまんま） • 開始と終了を持つ • 含まれているとか、結合・交差とかの演算子が定義さ れている • PostgreSQLでは任意の型を元に新しい範囲型を定義で きる Developing Time-Oriented Database Applications in SQLではPeriod型、 Temporal Data and the Relational ModelではInterval型と呼ばれているよ
3. 組み込みの範囲型 範囲型 元の型 離散的か int4range integer ◦ int8range bigint ◦ numrange numeric × tsrange timestamp × tstzrange timestamp with timezone × daterange date ◦
4. 範囲型の例（日付の範囲）
5. こうなります create table members ( birthday date, period daterange, name_en text ); insert into members(birthday, period, name_en) values ('1988-10-20', '[2001-08-26, 2012-05-18]', 'Risa Niigaki'), ('1988-12-23', '[2003-01-19, 2010-12-15]', 'Eri Kamei'), ('1989-11-11', '[2003-01-19, 2013-05-21]', 'Reina Tanaka'), ('1989-07-13', '[2003-01-19,]', 'Sayumi Michishige'), ('1985-02-26', '[2003-01-19, 2007-06-01]', 'Miki Fujimoto'), ...
6. 範囲型の例（整数型）
7. こうなります create table level1 ( level int, exp_range int4range, primary key(level), exclude using gist (exp_range with &&) ); insert into level1 values (1, '[0,11)'), (2, '[11,59)'), (3, '[59,164)');
8. 範囲型の作り方色々 '[0,10)' 0以上10未満 '[0,10]' 0以上10以下 '[0,)' 0以上（上限値なし） '[,)' 上限も下限もなし 'empty' 空の範囲 int4range(0, 10) コンストラクタ関数 [0,10)と同じ int4range(0, 10, '[]') [0,10]と同じ
9. 演算子、関数 http://www.postgresql.jp/document/9.3/html/functions- range.html
10. 離散的、正規化 -- 0以上11未満（11は含まない） -- 離散的な範囲型では正規化される =# select int4range '[0,10]'; int4range ----------- [0,11) (1 行) =# select v, int4range '[,11)' @> v from generate_series(10, 12) as s(v); v | ?column? ----+---------- 10 | t 11 | f 12 | f (3 行)
11. 使用例 -- 二人の在籍期間の重複日数 =# with q(n1, n2, p) as ( select m1.name_en, m2.name_en, m1.period * m2.period from members m1 join members m2 on(m1.name_en < m2.name_en) ) select n1, n2, upper(p) - lower(p) from q where not isempty(p) order by 3; n1 | n2 | ?column? ------------------+-------------------+---------- ... Reina Tanaka | Sayumi Michishige | 3776 Ai Takahashi | Risa Niigaki | 3688 Reina Tanaka | Risa Niigaki | 3408 Risa Niigaki | Sayumi Michishige | 3408
12. EXCLUDE制約 • UNIQUE制約は、同じものがないという制約 • EXCLUDE制約は、任意の2行に対して指定し た演算子が真とならない制約 • ＝演算子のEXCLUDE制約はUNIQUE制約と同 じ
13. 範囲型のEXCLUDE制約 =# select * from level1; level | exp_range -------+------------- 1 | [0,11) 2 | [11,59) 3 | [59,164) ... =# insert into level1 values(100, '[11,12)'); ERROR: 重複キーの値が排除制約 "level1_exp_range_excl" に 違反しています DETAIL: キー (exp_range)=([11,12)) が既存のキー (exp_range) =([11,59)) と競合しています
14. タイプ レベル 開始 終了 100万 1 0 11 100万 2 11 59 100万 3 59 164 150万 1 0 16 150万 2 16 99 150万 3 89 246
15. スカラ型と範囲型の組み合わせ -- gistは標準では = を使えない create extension btree_gist; create table level2 ( exp_type int, level int, exp_range int4range, primary key(exp_type, level), exclude using gist (exp_type with =, exp_range with &&) ); insert into level2 values (100, 1, '[0,11)'), (100, 2, '[11,59)'), ... (150, 1, '[0,16)'), (150, 2, '[16,89)'),
16. こんなに便利 • 境界を含む、含まないを表現できる • 上限、下限の無い範囲を表現できる • インデックスが効く • EXCLUDE制約で、重複していないことを保証 できる • 豊富な演算子