os: ubuntu 16.04
db: postgresql 9.6.8
pg_log_userqueries: 1.1.1

data type for storing sets of (key, value) pairs

该模块实现了 hstore 数据类型用来在一个单一PostgreSQL值中存储键值对。
这在很多情景下都有用，例如带有很多很少被检查的属性的行或者半结构化数据。
键和值都是简单的文本字符串。

版本

# lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
De ion:	Ubuntu 16.04.5 LTS
Release:	16.04
Codename:	xenial


$ psql
psql (9.6.8)
Type \"help\" for help.

postgres=# select version();
                                                                   version                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.8 on x86_64-pc-linux-gnu (Ubuntu 9.6.8-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 row)

postgres=# 

实验

postgres=# select *
from pg_available_extensions e
where 1=1
and e.name like \'%hstore%\'
;

postgres=# select *
from pg_extension pe
where 1=1
and pe.extname like \'%hstore%\'
;

postgres=# create extension hstore;

postgres=# create table tmp_t1(
 var hstore
)
;

postgres=# insert into tmp_t1 select hstore(pg_stat_activity.*) from pg_stat_activity;

postgres=# select pg_typeof(var), var from tmp_t1;

postgres=# create index idx_tmp_t1_x1 on tmp_t1 USING btree (var);

postgres=# create index idx_tmp_t1_x2 on tmp_t1 USING GIST (var);

postgres=# create index idx_tmp_t1_x3 on tmp_t1 USING GIN (var);

postgres=# create index idx_tmp_t1_x4 on tmp_t1 USING hash (var);

postgres=# \\d tmp_t1;
    Table \"public.tmp_t1\"
 Column |  Type  | Modifiers 
--------+--------+-----------
 var    | hstore | 
Indexes:
    \"idx_tmp_t1_x1\" btree (var)
    \"idx_tmp_t1_x2\" gist (var)
    \"idx_tmp_t1_x3\" gin (var)
    \"idx_tmp_t1_x4\" hash (var)

支持挺多类型的索引

select var -> \'pid\',
       var -> ARRAY[\'pid\',\'state\'],
       %% var, 
       %# var, 
       var 
  from tmp_t1

提取数据，太强大了。

参考:
http://postgres.cn/docs/9.6/hstore.html