转载自： http://francs3.blog.163.com/blog/static/40576727201281825129918/
hstore 数据类型作为 Extension 模块， 在之前版本已经有了，之前大概知道概念，一直没怎么研究，今天做了些测试。
简单的说 hstore 数据类型用来存储具有多个属性值的数据，模板如 key => value ， key 代表存储的属性， values 为相应属性对应的值，一个简单的 hstore 例子如下：
一、 hstore Example
skytf=> select 'a=>1,b=>2'::hstore;
hstore --------------------
"a"=>"1","b"=>"2"
(1 row)
skytf=> select hstore('a=>1,b=>2');
hstore --------------------
"a"=>"1","b"=>"2"
(1 row)
二、 hstore 操作符演示
skytf=> select hstore('a=>1,b=>2') -> 'a';
?column? ----------
1
(1 row)
- 2.2 判断是否包含指定属性(key)，如果包含，返回 t
skytf=> select hstore('a=>1,b=>2') ? 'a';
?column? ----------
t
(1 row)
skytf=> select hstore('a=>1,b=>2') ? 'c';
?column? ----------
f
(1 row)
- 2.3 判断左边的 hstore 是否包含右边的 hstore ，如果包含，返回 t。
skytf=> select hstore('a=>1,b=>2') @> 'a=>1'::hstore;
?column? ----------
t
(1 row)
skytf=> select hstore('a=>1,b=>2') @> 'a=>2'::hstore;
?column? ----------
f
(1 row)
备注：更多 hstore 函数和操作符，详见本文的附录部分。
三、 hstore 函数演示
- 3.1 array 类型转换成 hstore
skytf=> select hstore(ARRAY['a','1','b','2']);
hstore
--------------------
"a"=>"1","b"=>"2"
(1 row)
- 3.2 将两个 array 类型数据转换成 hstore 类型，比较难表达，看例子理解。
skytf=> select hstore(ARRAY['a','c'],ARRAY['1','2','3']);
hstore
------------------------------
"a"=>"1","b"=>"2","c"=>"3"
(1 row)
备注：看到了吧，非常强大。
- 3.3 将 hstore 类型数据的 key 转换成 array
skytf=> select akeys('a=>1,b=>2');
akeys -------
{a,b}
(1 row)
- 3.4 将 hstore 类型数据的 key 转换成 结果集
skytf=> select skeys('a=>1,b=>2');
skeys -------
a
b
(2 rows)
- 3.5 将 hstore 类型数据的 values 转换成 array
skytf=> select avals('a=>1,b=>2');
avals -------
{1,2}
(1 row)
- 3.6 将 hstore 类型数据的 values 转换成 结果集
skytf=> select svals('a=>1,b=>2');
svals -------
1
2
(2 rows)
skytf=> select delete(hstore('a=>1,b=>2'),'b');
delete ----------
"a"=>"1"
(1 row)
skytf=> select hstore('a=>1,b=>2') || 'c=>3';
?column?
------------------------------
"a"=>"1","c"=>"3"
(1 row)
备注：先演示这么多吧，更多内容，参考本文的附。
四、Example1 : hstore 使用
4.1 环境
PG版本：Postgresql 9.2
OS版本：Red Hat Enterprise Linux Server release 6.24.2 创建 hstore 外部模块
skytf=> \c skytf postgres;
You are now connected to database "skytf" as user "postgres".
skytf=# create extension hstore;
CREATE EXTENSION
skytf=# \c skytf skytf;
You are now connected to database "skytf" as user "skytf".
skytf=> \dT hstore;
List of data types
Schema | Name | Description
--------+--------+-------------
public | hstore |
(1 row)
- 4.3 创建测试表，并生成测试数据
skytf=> create table tbl_role(id serial primary key,role_name varchar(32),exp int8,wealth int8,status char(1));
NOTICE: CREATE TABLE will create implicit sequence "tbl_role_id_seq" for serial column "tbl_role.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tbl_role_pkey" for table "tbl_role"
CREATE TABLE
skytf=> insert into tbl_role(role_name,exp,wealth) select 'user_' || generate_series(1,100000),generate_series(1,generate_series(100001,200000);
INSERT 0 100000
skytf=> select * from tbl_role limit 3;
id | role_name | exp | wealth | status
----+-----------+-----+--------+--------
1 | user_1 | 1 | 100001 |
2 | user_2 | 2 | 100002 |
3 | user_3 | 3 | 100003 |
(3 rows)
备注：上面创建一张角色信息表，属性值有 exp:经验值， wealth:财富值等。
- 4.4 增加 hstore 数据类型
skytf=> alter table tbl_role add column attr hstore;
ALTER TABLE
skytf=> update tbl_role set attr=('exp=>' || exp || ',wealth=>' || wealth )::hstore;
UPDATE 100000
skytf=> select * from tbl_role limit 3;;
id | role_name | exp | wealth | status | attr
----+-----------+-----+--------+--------+---------------------------------
22 | user_22 | 22 | 100022 | | "exp"=>"22","wealth"=>"100022"
23 | user_23 | 23 | 100023 | | "exp"=>"23","wealth"=>"100023"
24 | user_24 | 24 | 100024 | | "exp"=>"24","wealth"=>"100024"
(3 rows)
- 4.5 创建索引
skytf=> create index concurrently idx_tbl_role_attr on tbl_role using GIST ( attr);
CREATE INDEX
备注： hstore 类型的数据支持 GIN,GIST 索引扫描的操作符有 @>,?,?& 和 ?|
- 4.6 测试：查询 hstore 子元素 exp 值为 22 的记录
skytf=> select id,role_name,attr,attr -> 'exp' From tbl_role where attr @> 'exp=>22';
id | role_name | attr | ?column?
----+-----------+---------------------------------+----------
22 | user_22 | "exp"=>"22","wealth"=>"100022" | 22
(1 row)
skytf=> select id,attr -> 'wealth' From tbl_role where attr @> 'wealth=>100001';
id | role_name | attr | ?column?
----+-----------+--------------------------------+----------
1 | user_1 | "exp"=>"1","wealth"=>"100001" | 100001
- 4.7 执行计划：
skytf=> explain analyze select id,attr -> 'exp' From tbl_role where attr @> 'exp=>22';
QUERY PLAN
--------------------------------------------------------------------
Bitmap Heap Scan on tbl_role (cost=5.36..347.63 rows=100 width=54) (actual time=3.267..7.111 rows=1 loops=1)
Recheck Cond: (attr @> '"exp"=>"22"'::hstore)
Rows Removed by Index Recheck: 1545
-> Bitmap Index Scan on idx_tbl_role_attr (cost=0.00..5.33 rows=100 width=0) (actual time=2.813..2.813 rows=1546 loops=1)
Index Cond: (attr @> '"exp"=>"22"'::hstore)
Total runtime: 7.185 ms
(6 rows)
备注：消耗 7 ms 左右，这个查询速度并不很快。
五、Example2 ：将结果集转换成 hstore 类型
- 5.1 测试表
skytf=> select * From test_1;
id | name ----+--------
1 | francs
2 | fpzhou
(2 rows)
- 5.2 将结果集转换成 hstore 类型输出
skytf=> select hstore(test_1) From test_1;
hstore
-----------------------------
"id"=>"1","name"=>"francs"
"id"=>"2","name"=>"fpzhou"
(2 rows)
skytf=> select hstore(test_1) From test_1 where id=1;
hstore
-----------------------------
"id"=>"1","name"=>"francs"
(1 row)
