15日目を担当させていただくことになりましたf-murakamiです。
よろしくお願いします。
PostgreSQLにはhstore型という便利な型があります。
本稿では、そのhstore型のSQLでの構文について、自分の備忘録もかねて、まとめてみようと思います。
PostgreSQLのhstore型について簡単に
PostgreSQLのhstore型とは、キーとバリューの集合を１つの列に管理できる型です。
つまり、複数の項目を１つの列で管理できるので、テーブルに列を追加しなくても、キーを追加するだけで項目を手軽に増やせることが大きなメリットです。
ただその分、管理をきっちりしないと、あとでどういう項目が入ってくるんだっけ、って分からなくなったりするので注意が必要です。
ちなみに、Ruby on Railsなら rails4からサポートしています。
（PostgreSQLのhstore extensionを有効にする必要があります）
hstore型をSQLで表現するワケ
最近はORMが充実していますので、SQLをゴリゴリ書くことは無いかもしれません。
それでも、
- データベースの内容を表形式でさっと確認したいとき
- 複数テーブルをjoinしないと取得できないようなとき
- データベースのデータを直接修正しないといけないとき
のような状況があったり、
上司や他部署の人たちから、データベースの保守スクリプトのレビューを受けるときに、
「プログラムじゃ分からないよ。SQLで書いてよ！」
って言われたり。
（かくいう私も、一昔前はバリバリSQLを書いていた世代でした ^^;）
このようなときに、hstore型ってSQLでどう表記するんだっけ、と毎回ググるのも面倒なんで、さっと確認できるようまとめておこうと思います。
（意外にこの手の内容がまとまったサイトはあまりない感じです）
前置きが長くなりましたが、これよりhstore型のSQLの構文を見ていきましょう。
hstore型における基本的なSQL構文
hstore型を操作する構文は多々あるのですが、基本的な4つの構文に絞りました。
以下の4構文さえ押さえておけば、だいたいの場面はカバーできるんじゃないかと思ってます。
(1)hstore型カラムのキーを指定して値を取得するには
varchar型
select column_name as xxx from table_name
hstore型
select column_name -> 'key_name' as xxx from table_name
例） accountsテーブルのtags(hstore型カラム)から、start_dateというキーの値を取得する
select
id
,account_number
,tags -> 'start_date' as start_date
from
accounts;
結果）
id | account_number | start_date
----+-----------------+------------
1 | 00000001 | 2016-01-01
2 | 00000002 | 2016-03-01
3 | 00000003 | 2016-12-10
(2)hstore型カラムのキーの値を条件するには
varchar型
where column_name = 'xxxx'
hstore型
where column_name -> 'key_name' = 'xxxx'
例） accountsテーブルのtags(hstore型カラム)にあるstart_dateというキーの値が'2016-01-01'以降のデータを取得する
select
*
from
accounts
where
tags -> 'start_date' >= '2016-01-01';
(3)hstore型カラムにキーが存在するかどうかを条件にするには
hstore型
-- キーが存在するとき
where exist(column_name,'key_name') = true
-- キーが存在しないとき
where exist(column_name,'key_name') = false
例） accountsテーブルのtags(hstore型カラム)にstart_dateというキーが存在するデータを取得する
select
*
from
accounts
where
exist(tags,'start_date') = true;
(4)hstore型カラムのキーを指定して、値を更新するには
varchar型
update table_name set column_name = 'xxxx' where <条件>;
hstore型
update table_name set column_name = column_name || hstore('key_name','xxxx') where <条件>;
例） accountsテーブルのtags(hstore型カラム)にあるstart_dateというキーの値を'2016-02-01'に更新する
update accounts set tags = tags || hstore('start_date','2016-02-01') where id=xxx;
まとめ
PostgreSQLのhstore型を操作する基本的なSQL構文についてまとめてみました。
利用する場面はあまりないかもしれませんが、いざというときのお役に立つことができれば幸いです。
それでは。