知识积累

sean's blog

0%

PostgreSQL中使用hstore存储键值对数据

发表于 更新于 分类于 ，

Hstore 数据类型可以把键值对集合存储在PGSQL的一个单独的值中

  • 键值对无序存储
  • 每个键是唯一的
  • 值可以为NULL，例如：key=>NULL

安装

postgres-$ create extension hstore;
CREATE EXTENSION

建表

postgres=# create table hstore_test(item_id serial, data hstore);
NOTICE:  CREATE TABLE will create implicit sequence "hstore_test_item_id_seq" for serial column "hstore_test.item_id"
CREATE TABLE

操作符

操作符 描述 举例 结果
hstore -> text 取某个key对用的值，没有则返回NULL ‘a=>x, b=>y’::hstore -> ‘a’ x
hstore -> text[] 取一组keys对应的值，没有则返回NULL ‘a=>x, b=>y, c=>z’::hstore -> ARRAY[‘c’,’a’] {“z”,”x”}
text => text 生成键值对 ‘a’ => ‘b’ “a”=>”b”
hstore || hstore hstore取并集 'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore “a”=>”b”, “c”=>”x”, “d”=>”q”
hstore ? text 是否包含key ‘a=>1’::hstore ? ‘a’ t
hstore ?& text[] 是否包含所有key ‘a=>1,b=>2’::hstore ?& ARRAY[‘a’,’b’] t
hstore ?| text[] 是否包含其中一个key 'a=>1,b=>2'::hstore ?| ARRAY['b','c'] t
hstore @> hstore 左侧hstore是否包含右侧hstore ‘a=>b, b=>1, c=>NULL’::hstore @> ‘b=>1’ t
hstore <@ hstore 同上，反向 ‘a=>c’::hstore <@ ‘a=>b, b=>1, c=>NULL’ f
hstore - text 根据key删除一个键值对 ‘a=>1, b=>2, c=>3’::hstore - ‘b’::text “a”=>”1”, “c”=>”3”
hstore - text[] 根据key删除一组键值对 ‘a=>1, b=>2, c=>3’::hstore - ARRAY[‘a’,’b’] “c”=>”3”
hstore - hstore 根据key/value删除一组键值对 ‘a=>1, b=>2, c=>3’::hstore - ‘a=>4, b=>2’::hstore “a”=>”1”, “c”=>”3”
record #= hstore 全局替换replace see Examples section
%% hstore 键值对转换成数组 %% ‘a=>foo, b=>bar’::hstore {a,foo,b,bar}
%# hstore 键值对转换成二维数组 %# ‘a=>foo, b=>bar’::hstore {{a,foo},{b,bar}}

函数

函数 返回类型 描述 举例 结果
hstore(record) hstore 根据一行结构化数据构建键值对 hstore(ROW(1,2)) f1=>1,f2=>2
hstore(text[]) hstore 基于key:value一个数组构建hstore hstore(ARRAY['a','1','b','2']) || hstore(ARRAY[['c','3'],['d','4']]) a=>1, b=>2, c=>3, d=>4
hstore(text[], text[]) hstore 基于keys和values两个数组构建hstore hstore(ARRAY[‘a’,’b’], ARRAY[‘1’,’2’]) “a”=>”1”,”b”=>”2”
hstore(text, text) hstore 构建一个键值对 hstore(‘a’, ‘b’) “a”=>”b”
akeys(hstore) text[] 以数组形式输出hstore的所有keys akeys(‘a=>1,b=>2’) {a,b}
skeys(hstore) setof text 以集合形式输入hstore的所有keys skeys(‘a=>1,b=>2’) (a,b)
avals(hstore) text[] 以数组形式输出hstore的所有values avals(‘a=>1,b=>2’) {1,2}
svals(hstore) setof text 以集合形式输入hstore的所有values svals(‘a=>1,b=>2’) (1,2)
hstore_to_array(hstore) </br>text[] 以数组形式输出hstore的所有key/values hstore_to_array(‘a=>1,b=>2’) </br>{a,1,b,2}
hstore_to_matrix(hstore) </br>text[] 以二维数组形式输出hstore的所有key/values hstore_to_matrix(‘a=>1,b=>2’) </br>{{a,1},{b,2}}
slice(hstore, text[]) hstore 抽取hstore的子集 slice(‘a=>1,b=>2,c=>3’::hstore, ARRAY[‘b’,’c’,’x’]) “b”=>”2”, “c”=>”3”
each(hstore) setof(key text, value text) 展开hstore的key/values为多行 select * from each(‘a=>1,b=>2’)
exist(hstore,text) boolean 是否存在一个key exist(‘a=>1’,’a’) t
defined(hstore,text) boolean 是否存在一个value not null的key defined(‘a=>NULL’,’a’) f
delete(hstore,text) hstore 根据key删除键值对 delete(‘a=>1,b=>2’,’b’) “a”=>”1”
delete(hstore,text[]) hstore 根据一组keys删除键值对 delete(‘a=>1,b=>2,c=>3’,ARRAY[‘a’,’b’]) “c”=>”3”
delete(hstore,hstore) hstore 根据确切的键值对删除 delete(‘a=>1,b=>2’,’a=>4,b=>2’::hstore) “a”=>”1”
populate_record(record,hstore) record 全局替换 see Examples section

插入

postgres=# INSERT INTO hstore_test (data) VALUES ('"key1"=>"value1", "key2"=>"value2", "key3"=>"value3"');
INSERT 0 1
postgres=# select * from hstore_test;
 item_id |                         data                         
 ---------+------------------------------------------------------
        1 | "key1"=>"value1", "key2"=>"value2", "key3"=>"value3"
(1 row)

更新

-- 删除一个键值对
postgres=# UPDATE hstore_test SET data = delete(data, 'key2')
postgres-# ;
UPDATE 1
postgres=# select * from hstore_test;
 item_id |                data                
 ---------+------------------------------------
        1 | "key1"=>"value1", "key3"=>"value3"
(1 row)

-- 增加一个键值对或修改一个已存在键的值
postgres=# UPDATE hstore_test SET data = data || '"key4"=>"some value"'::hstore;
UPDATE 1
postgres=# select * from hstore_test;
 item_id |                           data                           
 ---------+----------------------------------------------------------
        1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)

查询

-- 包含某个键
postgres=# SELECT * FROM hstore_test WHERE data ? 'key4';
 item_id |                           data                           
 ---------+----------------------------------------------------------
        1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
                (1 row)

-- 不包含某个键
postgres=# SELECT * FROM hstore_test WHERE NOT data ? 'key5';
 item_id |                           data                           
 ---------+----------------------------------------------------------
        1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
                (1 row)

-- 包含某个键值对
postgres=# SELECT * FROM hstore_test WHERE data @> '"key4"=>"some value"'::hstore;
 item_id |                           data                           
 ---------+----------------------------------------------------------
        1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
                (1 row)

-- 查询某个键的值
postgres=# SELECT data -> 'key4' FROM hstore_test;
 ?column?  
------------
 some value
 (1 row)

-- 将键值对展开成多条
postgres=# SELECT item_id, (each(data)).* FROM hstore_test WHERE item_id = 1;
 item_id | key  |   value    
 ---------+------+------------
       1 | key1 | value1
       1 | key3 | value3
       1 | key4 | some value
(3 rows)

与结构化数据结构相互转化

  • 结构化 -> Hstore
    <!—hexoPostRenderEscape:
    CREATE TABLE test (col1 integer, col2 text, col3 text);
    INSERT INTO test VALUES (123, ‘foo’, ‘bar’);

SELECT hstore(t) FROM test AS t;
store
——————————————————————
“col1”=“123”, “col2”=“foo”, “col3”=“bar”
(1 row)

  • Hstore -> 结构化
    <!—hexoPostRenderEscape:
    CREATE TABLE test (col1 integer, col2 text, col3 text);

SELECT * FROM populate_record(null::test,‘“col1”=>”456”, “col2”=>”zzz”‘);
col1 | col2 | col3
———+———+———
456 | zzz |
(1 row)

统计

SELECT key, count(*) FROM
  (SELECT (each(h)).key FROM testhstore) AS stat
      GROUP BY key
          ORDER BY count DESC, key;
key    | count
-----------+-------
 line      |   883
 query     |   207
 pos       |   203
 node      |   202
 space     |   197
 status    |   195
 public    |   194
 title     |   190
 org       |   189
 ...................

索引

  • 使用GIST或GIN索引来支持 @>,?,?&,?/等查询操作符
    CREATE INDEX hidx ON testhstore USING GIST (h);
CREATE INDEX hidx ON testhstore USING GIN (h);
  • 也可以使用BTREE或HASH索引，用来支持=，groupby,orderby,distinct查询
    CREATE INDEX hidx ON testhstore USING BTREE (h);
CREATE INDEX hidx ON testhstore USING HASH (h);