PostgreSQL: hstore 数据类型使用介绍

转载自： 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 操作符演示

• 2.1 查询属性(key) a 的值；

 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)

• 3.7 删除一个属性

skytf=> select delete(hstore('a=>1,b=>2'),'b');
 delete ----------
 "a"=>"1"
(1 row)

• 3.8 增加一个属性

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.2

• 4.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)