Help us understand the problem. What is going on with this article?

PostgreSQLのhstore型を操作するSQL構文まとめ

More than 3 years have passed since last update.

15日目を担当させていただくことになりましたf-murakamiです。
よろしくお願いします。

PostgreSQLにはhstore型という便利な型があります。
本稿では、そのhstore型のSQLでの構文について、自分の備忘録もかねて、まとめてみようと思います。

PostgreSQLのhstore型について簡単に

PostgreSQLのhstore型とは、キーとバリューの集合を1つの列に管理できる型です。

つまり、複数の項目を1つの列で管理できるので、テーブルに列を追加しなくても、キーを追加するだけで項目を手軽に増やせることが大きなメリットです。

ただその分、管理をきっちりしないと、あとでどういう項目が入ってくるんだっけ、って分からなくなったりするので注意が必要です。

ちなみに、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構文についてまとめてみました。
利用する場面はあまりないかもしれませんが、いざというときのお役に立つことができれば幸いです。
それでは。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした