hstore扩展（KV存储类型）

hstore¶

hstore模块实现了一种hstore数据类型，用来在一个数据库的值中存储键值对（key-value）。在很多实际场景中可直接使用，例如带有很多很少被检查的属性的行或者半结构化数据。其中key和value都是简单的文本字符串。

hstore作者为：Oleg Bartunov oleg@sai.msu.su，俄罗斯莫斯科大学； Teodor Sigaev teodor@sigaev.ru，俄罗斯德尔塔软件有限公司；额外的提升由英国的 Andrew Gierth andrew@tao11.riddles.org.uk 提供。

hstore数据类型外部表现¶

一个hstore类型用于输入和输出的文本表示为，包括零个或者多个由逗号分隔的key => value对。例如：

k => v
foo => bar, baz => whatever
"1-a" => "anything at all"

其中，键值对的顺序没有意义（并且在输出时也不会重现）。键值对之间或者=>号周围的空白会被忽略。双引号内的键和值可以包括空白、逗号、=或>。要在一个key或value中包括一个双引号或一个反斜线，需用反斜线对其转义。

一个hstore类型中的每一个key是唯一的。如果声明了一个有重复键的hstore，只有一个会被存储在hstore中且无法保证哪一个将被保留，如下：

``` sql SELECT 'a=>1,a=>2'::hstore; hstore

"a"=>"1" ```

value可以为null，但key不允许为null。例如：

seaboxsql=# SELECT 'key => NULL'::hstore;
   hstore
-------------
 "key"=>NULL
(1 row)

NULL关键词是大小写不敏感的。将NULL放在双引号中可以将它当作一个普通的字符串“NULL”。

hstore 操作符和函数¶

`->`¶

语法: hstore -> text|text[]
描述: 为键或多个键得到值（不存在则是NULL）
注意事项: 无
示例: ``` sql seaboxsql=# select 'a=>x, b=>y'::hstore -> 'a'; ?column?
x (1 row) seaboxsql=# select 'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a']; ?column?
{z,x} (1 row) ```

`||`¶

语法: hstore || hstore
描述: 串接hstore
注意事项: 无
示例: ``` sql seaboxsql=# select 'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore; ?column?
"a"=>"b", "c"=>"x", "d"=>"q" (1 row) ```

`?`¶

语法: hstore ? text
描述: hstore是否包含键？
注意事项: 无
示例: ``` sql seaboxsql=# select 'a=>1'::hstore ? 'a'; ?column?
t (1 row) ```

`?`¶

语法: hstore ? text
描述: hstore是否包含键？
注意事项: 无
示例: ``` sql seaboxsql=# select 'a=>1'::hstore ? 'a'; ?column?
t (1 row) ```

`?&`¶

语法: hstore ?& text[]
描述: hstore是否包含所有指定的键？
注意事项: 无
示例: ``` sql seaboxsql=# select 'a=>1,b=>2'::hstore ?& ARRAY['a','b']; ?column?
t (1 row) ```

`?|`¶

语法: hstore ?| text[]
描述: hstore是否包含任何指定的键？
注意事项: 无
示例: ``` sql seaboxsql=# select 'a=>1,b=>2'::hstore ?| ARRAY['b','c']; ?column?
t (1 row) ```

`@>`¶

语法: hstore @> hstore
描述: 左操作数是否包含右操作数？
注意事项: 无
示例: ``` sql seaboxsql=# select 'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'; ?column?
t (1 row) ```

`<@`¶

语法: hstore <@ hstore
描述: 左操作数是否被包含在右操作数中？
注意事项: 无
示例: ``` sql seaboxsql=# select 'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL'; ?column?
f (1 row) ```

`-`¶

语法

hstore - text|text[]|hstore

描述

从左操作数中删除键或多个键值，当-操作符后为hstore类型时，删除匹配对

注意事项

无

示例

``` sql seaboxsql=# select 'a=>1, b=>2, c=>3'::hstore - 'b'::text; ?column?

"a"=>"1", "c"=>"3" (1 row)

seaboxsql=# select 'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b']; ?column?

"c"=>"3" (1 row)

seaboxsql=# select 'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore; ?column?

"a"=>"1", "c"=>"3" (1 row) ```

`#=`¶

语法

record #= hstore

描述

用来自hstore的匹配值替换record中的域

注意事项

无

示例

``` sql seaboxsql=# CREATE TABLE test (col1 integer, col2 text, col3 text); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause – Using column named 'col1' as the Seabox Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.

CREATE TABLE seaboxsql=# INSERT INTO test VALUES (123, 'foo', 'bar'); INSERT 0 1 seaboxsql=# seaboxsql=# SELECT ®.* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s; col1 | col2 | col3 ------+------+------ 123 | foo | baz (1 row) ```

`%%`¶

语法: %% hstore
描述: 把hstore转换成键和值交替出现的数组
注意事项: 无
示例: ``` sql seaboxsql=# select %% 'a=>foo, b=>bar'::hstore; ?column?
{a,foo,b,bar} (1 row) ```

`%#`¶

语法: %# hstore
描述: 把hstore转换成二维的键值数组
注意事项: 无
示例: ``` sql seaboxsql=# select %# 'a=>foo, b=>bar'::hstore; ?column?
{{a,foo},{b,bar}} (1 row) ```

`hstore()`¶

语法

hstore(record)|hstore(text[])|hstore(text[], text[])|hstore(text, text)

返回类型

hstore

描述

构造一个hstore类型的数据，可通过记录或行，也可通过数组（数组可以是一个键值数组或者一个二维数组），或通过独立的键和值数组构建，也可通过指定text类型参数构造构造单一项的hstore数据。

注意事项

无

示例

``` sql seaboxsql=# select hstore(ROW(1,2)); hstore

"f1"=>"1", "f2"=>"2" (1 row)

seaboxsql=# select hstore(ARRAY['a','1','b','2']) || hstore(ARRAY[['c','3'],['d', '4']]); ?column?

"a"=>"1", "b"=>"2", "c"=>"3", "d"=>"4" (1 row)

seaboxsql=# select hstore(ARRAY['a','b'], ARRAY['1','2']); hstore

"a"=>"1", "b"=>"2" (1 row)

seaboxsql=# select hstore('a', 'b'); hstore

"a"=>"b" (1 row) ```

`akeys()`¶

语法: akeys(hstore)
返回类型: text[]
描述: 取得hstore的键作为一个数组。
注意事项: 无
示例: ``` sql seaboxsql=# select akeys('a=>1,b=>2'); akeys
{a,b} (1 row) ```

`skeys()`¶

语法: skeys(hstore)
返回类型: setof text
描述: 取得hstore的键作为一个集合。
注意事项: 无
示例: ``` sql seaboxsql=# select skeys('a=>1,b=>2'); skeys
a b (2 rows) ```

`avals()`¶

语法: avals(hstore)
返回类型: text[]
描述: 取得hstore的值作为一个数组。
注意事项: 无
示例: ``` sql seaboxsql=# select avals('a=>1,b=>2'); avals
{1,2} (1 row) ```

`svals()`¶

语法: svals(hstore)
返回类型: setof text
描述: 取得hstore的值作为一个集合。
注意事项: 无
示例: ``` sql seaboxsql=# select svals('a=>1,b=>2'); svals
1 2 (2 rows) ```

`hstore_to_array()`¶

语法: hstore_to_array(hstore)
返回类型: text[]
描述: 取得hstore的键和值作为一个键和值交替出现的数组。
注意事项: 无
示例: ``` sql seaboxsql=# select hstore_to_array('a=>1,b=>2'); hstore_to_array
{a,1,b,2} (1 row) ```

`hstore_to_matrix()`¶

语法: hstore_to_matrix(hstore)
返回类型: text[]
描述: 取得hstore的键和值作为一个二维的数组。
注意事项: 无
示例: ``` sql seaboxsql=# select hstore_to_matrix('a=>1,b=>2'); hstore_to_matrix
{{a,1},{b,2}} (1 row) ```

`hstore_to_json()`¶

语法: hstore_to_json(hstore)
返回类型: json
描述: 取得hstore作为一个json值，把所有非空值转换为 JSON 字符串。
注意事项: 无
示例: ``` sql seaboxsql=# select hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4'); hstore_to_json
{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"} (1 row) ```

`hstore_to_jsonb()`¶

语法: hstore_to_jsonb(hstore)
返回类型: jsonb
描述: 取得hstore作为一个jsonb值，把把所有非空值转换为 JSON 字符串。
注意事项: 无
示例: ``` sql seaboxsql=# select hstore_to_jsonb('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4'); hstore_to_jsonb
{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"} (1 row) ```

`hstore_to_json_loose()`¶

语法: hstore_to_json_loose(hstore)
返回类型: json
描述: 取得hstore作为一个json值，但是尝试区分数字值和布尔值这样它们在 JSON 中无需引用。
注意事项: 无
示例: ``` sql seaboxsql=# select hstore_to_json_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4'); hstore_to_json_loose
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "a key": 1} (1 row) ```

`hstore_to_jsonb_loose()`¶

语法: hstore_to_jsonb_loose(hstore)
返回类型: jsonb
描述: 取得hstore作为一个jsonb值，但是尝试区分数字值和布尔值这样它们在 JSON 中无需引用。
注意事项: 无
示例: ``` sql seaboxsql=# select hstore_to_jsonb_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4'); hstore_to_jsonb_loose
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 23450, "a key": 1} (1 row) ```

`slice()`¶

语法: slice(hstore, text[])
返回类型: hstore
描述: 从一个hstore抽取一个子集。
注意事项: 无
示例: ``` sql seaboxsql=# select slice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x']); slice
"b"=>"2", "c"=>"3" (1 row) ```

`each()`¶

语法: each(hstore)
返回类型: setof(key text, value text)
描述: 取得hstore的键和值作为一个集合。
注意事项: 无
示例: sql seaboxsql=# select * from each('a=>1,b=>2'); key | value -----+------- a | 1 b | 2 (2 rows)

`exist()`¶

语法: exist(hstore,text)
返回类型: boolean
描述: hstore是否包含键？
注意事项: 无
示例: ``` sql seaboxsql=# select exist('a=>1','a'); exist
t (1 row) ```

`defined()`¶

语法: defined(hstore,text)
返回类型: boolean
描述: hstore是否为键包含非NULL值？
注意事项: 无
示例: ``` sql seaboxsql=# select defined('a=>NULL','a'); defined
f (1 row) ```

`delete()`¶

语法

delete(hstore,text)|delete(hstore,text[])|delete(hstore,hstore)

返回类型

hstore

描述

删除匹配键的对，或删除匹配多个键的多个对，抑或删除匹配第二个参数的对

注意事项

无

示例

``` sql seaboxsql=# select delete('a=>1,b=>2','b'); delete

"a"=>"1" (1 row)

seaboxsql=# select delete('a=>1,b=>2,c=>3',ARRAY['a','b']); delete

"c"=>"3" (1 row)

seaboxsql=# select delete('a=>1,b=>2','a=>4,b=>2'::hstore); delete

"a"=>"1" (1 row) ```

`populate_record()`¶

语法

populate_record(record,hstore)

返回类型

record

描述

用来自hstore的匹配值替换record中的域

注意事项

无

示例

``` sql seaboxsql=# CREATE TABLE test (col1 integer, col2 text, col3 text); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause – Using column named 'col1' as the Seabox Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. CREATE TABLE

seaboxsql=# SELECT * FROM populate_record(null::test,'"col1"=>"456", "col2"=>"zzz"'); col1 | col2 | col3 ------+------+------ 456 | zzz | (1 row) ```

索引¶

hstore的@>、?、?&和?|操作符支持 GiST 和 GIN 索引。例如：

CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);

hstore的=操作符也支持支持btree或hash索引。允许hstore列被声明为UNIQUE或者被使用在GROUP BY、ORDER BY或DISTINCT表达式中。hstore值的排序顺序不是特别有用，但是这些索引可能对等值查找有用。可为=比较创建以下索引：

CREATE INDEX hidx ON testhstore USING BTREE (h);

CREATE INDEX hidx ON testhstore USING HASH (h);

实际使用示例¶

增加一个键，或者用一个新值更新一个现有的键：

UPDATE tab SET h = h || hstore('c', '3');

删除一个键：

UPDATE tab SET h = delete(h, 'k1');

将record转换成hstore：

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT hstore(t) FROM test AS t;
                   hstore                    
---------------------------------------------
 "col1"=>"123", "col2"=>"foo", "col3"=>"bar"
(1 row)

将hstore转换成预定义的record类型：

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)

用hstore的值修改一个现有的记录：

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s;
 col1 | col2 | col3 
------+------+------
  123 | foo  | baz
(1 row)

检查及统计¶

hstore类型本质是宽松的结构，允许包含一些不同的键；检查key是否合法设计中归应用负责。以下示例用于检查key是否合法以及统计运算。

检查键是否合法

seaboxsql=# SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');
 key | value
-----+-------
     | 1
 b   |
 aaa | bq
(3 rows)

使用表中hstore数据

 CREATE TABLE testhstore (id SERIAL, h hstore);
 NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Seabox Database data distribution key for this table.
 HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
 CREATE TABLE
 
 seaboxsql=# INSERT INTO testhstore (h) VALUES ('name=>smallfish, age=>29'::hstore);
 INSERT 0 1
 
 seaboxsql=# INSERT INTO testhstore (h) VALUES ('name=>nnfish, age=>20'::hstore);
 INSERT 0 1
 
 seaboxsql=# INSERT INTO testhstore (h) VALUES ('name=>aaa, age=>30, addr=>China'::hstore);
 INSERT 0 1
 
 seaboxsql=# SELECT * FROM testhstore;
  id |                      h
 ----+---------------------------------------------
   3 | "age"=>"30", "addr"=>"China", "name"=>"aaa"
   2 | "age"=>"20", "name"=>"nnfish"
   1 | "age"=>"29", "name"=>"smallfish"
 (3 rows)
 
 seaboxsql=# SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;
 NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'key' as the Seabox Database data distribution key for this table.
 HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
 SELECT 7
 seaboxsql=# select * from stat;
  key  |   value
 ------+-----------
  addr | China
  name | aaa
  name | nnfish
  name | smallfish
  age  | 30
  age  | 20
  age  | 29
 (7 rows)

统计查询：

seaboxsql=# SELECT key, count(*) FROM
seaboxsql-#   (SELECT (each(h)).key FROM testhstore) AS stat
seaboxsql-#   GROUP BY key
seaboxsql-#   ORDER BY count DESC, key;
 key  | count
------+-------
 age  |     3
 name |     3
 addr |     1
(3 rows)

hstore扩展（KV存储类型）

hstore¶

hstore数据类型外部表现¶

hstore 操作符和函数¶

->¶

||¶

?¶

?¶

?&¶

?|¶

@>¶

<@¶

-¶

#=¶

%%¶

%#¶

hstore()¶

akeys()¶

skeys()¶

avals()¶

svals()¶

hstore_to_array()¶

hstore_to_matrix()¶

hstore_to_json()¶

hstore_to_jsonb()¶

hstore_to_json_loose()¶

hstore_to_jsonb_loose()¶

slice()¶

each()¶

exist()¶

defined()¶

delete()¶

populate_record()¶

索引¶

实际使用示例¶

检查及统计¶

`->`¶

`||`¶

`?`¶

`?`¶

`?&`¶

`?|`¶

`@>`¶

`<@`¶

`-`¶

`#=`¶

`%%`¶

`%#`¶

`hstore()`¶

`akeys()`¶

`skeys()`¶

`avals()`¶

`svals()`¶

`hstore_to_array()`¶

`hstore_to_matrix()`¶

`hstore_to_json()`¶

`hstore_to_jsonb()`¶

`hstore_to_json_loose()`¶

`hstore_to_jsonb_loose()`¶

`slice()`¶

`each()`¶

`exist()`¶

`defined()`¶

`delete()`¶

`populate_record()`¶