PostgreSQL: hstore 数据类型使用介绍

最新推荐文章于 2020-03-05 06:00:00 发布

一白.

最新推荐文章于 2020-03-05 06:00:00 发布

阅读量3.8k

点赞数

分类专栏： PostgreSQL 扩展文章标签： PostgreSQL hstore

PostgreSQL 扩展专栏收录该内容

6 篇文章 0 订阅

订阅专栏

转载自： 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','b','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", "b"=>"2", "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,100000),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,role_name,attr,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,role_name,attr,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)