hstore

はじめに

hstoreは PostGIS と同様に、PostgreSQL の拡張機能の一つである。 一つの hstore型カラムにキー、値の組み合わせの集合を格納できる。 ハッシュ構造で管理されているため、迅速に取り出せる。

ここで、キー、値は単純なテキスト文字列である。

使うときには次のようにする。

create extension hstore;

hstore型カラム

テーブル作成(hstore型カラムの宣言)

テーブル作成では例えば次のようにする。この例では tags が hstore型カラムである。

CREATE TABLE my_osmdata
(
  osm_id bigint,
  way_area real,
  key text,
  val text,
  name text,
  tags hstore,
  z_order int,
  way geometry(Geometry,3857)
)

hstore型カラムからあるキーに対する値を取り出す

hstore型カラム tags からキー ele の値を取り出すときは tags->'ele' とする。

例えば select 文では、もし、ele がmy_osmdataテーブルにカラムとして存在する場合には、

select ele from my_osmdata where ... 

独立カラムではなく、キーele の値が hstore型カラム tags に格納されているときには、

select tags->'ele' as ele from my_osmdata where ... 

hstore型カラムからキーを削除

delete(hstore,text)関数はキーに一致する項目を全て削除する。

hstore型カラム tags からキー'dup'を削除してみる。

-- 削除前の状態を確認する
select count(*) from my_osmdata where tags->'dup' is not null;
 count 
-------
 18777
(1 row)

-- キー'dup' を削除する
update my_osmdata set tags = delete(tags, 'dup') where tags->'dup' is not null;
-- UPDATE 18777

-- 削除後の状態を確認する
select count(*) from my_osmdata where tags->'dup' is not null;
 count 
-------
 0
(1 row)

update my_osmdata set tags = delete(tags,'icon') where key='shop' and val='funeral_directors';

hstore型カラムの更新

hstore型関数

多くあるが、実際に使用したものを追記してゆく。

hstore('キー','値')

キー('dup')を追加、または、既存のキー('dup')を新しい値('yes')で更新する。

update my_osmdata set tags = tags || hstore('dup', 'yes') where ... ;

ただし、tags = tags || hstore('dup', 'yes') は実行前の tags が null でないことを想定している。 もし、null の場合、null || テキスト は null となり、何もセットされない。

例えば set tags = (case when small.tags is null then '' else small.tags end) とすれば、必ず追加される。他の言語に比べて、case when ... else ... end はやや見づらい。

delete(カラム,'キー')

hstore型カラムからキーを削除する。

update my_osmdata set tags = delete(tags, 'dup') where tags->'dup' is not null;

参考文献