F.16. hstore

这个模块实现了hstore数据类型,在单个PostgreSQL值中存储一组键/值对。 这在不同的场景中是有用的,如很少检查带有许多属性的行,或半结构化的数据。 键和值是简单的文本字符串。

F.16.1. hstore 外部表示

hstore的文本表示用于输入和输出,包括零个或更多由逗号分开的 _key_ => _value_对。一些例子:

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

键/值对的顺序不重要(可能不在输出中复制)。忽略对和=>符号周围的空格。 包含空格、逗号、=>的键和值要加双引号。 要在键或值中包含一个双引号或反斜杠,要用反斜杠逃逸。

hstore中的每个键都是唯一的。如果你用重复键声明一个hstore, 将只有一个存储在hstore中,并且不保证会保存哪一个:

SELECT 'a=>1,a=>2'::hstore;
  hstore
----------
 "a"=>"1"

一个值(不是一个键)可以是SQL NULL。如:

key => NULL

NULL关键字是大小写敏感的。要将NULL 当做普通的字符串来对待就要给NULL加双引号。

Note: 记住hstore文本格式,当用于输入时,在任何请求的引用或逃逸之前应用。 如果通过一个参数传递一个hstore文本,那么不需要额外的处理。 但是如果作为引用的文本常量传递,那么任何单引号字符和(依赖于 standard_conforming_strings配置参数的设置)反斜杠字符需要正确的逃逸。 参阅Section 4.1.2.1获取更多处理字符串常量的信息。

在输出时,键和值总是包含在双引号中,即使并不严格需要也是这样。

F.16.2. hstore 操作符和函数

hstore模块提供的操作符显示在Table F-6中, 函数在Table F-7中。

Table F-6. hstore 操作符

操作符 描述 示例 结果
hstore -> text 获得键的值(如果不存在为NULL) 'a=>x, b=>y'::hstore -> 'a' x
hstore -> text[] 获得多个键的值(如果不存在为NULL) 'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a'] {"z","x"}
hstore || hstore 连接 hstore 'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore "a"=>"b", "c"=>"x", "d"=>"q"
hstore ? text hstore 包含键吗? 'a=>1'::hstore ? 'a' t
hstore ?& text[] hstore 包含所有指定的键? 'a=>1,b=>2'::hstore ?& ARRAY['a','b'] t
hstore ?| text[] hstore 包含任何指定的键? 'a=>1,b=>2'::hstore ?| ARRAY['b','c'] t
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 从左操作符中删除键 'a=>1, b=>2, c=>3'::hstore - 'b'::text "a"=>"1", "c"=>"3"
hstore - text[] 从左操作符中删除键 'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b'] "c"=>"3"
hstore - hstore 从左操作符中删除匹配对 'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore "a"=>"1", "c"=>"3"
record #= hstore hstore里匹配的值替换record里的字段 查看示例章节
%% hstore 转换hstore为替换键和值的数组 %% 'a=>foo, b=>bar'::hstore {a,foo,b,bar}
%# hstore 转换hstore为两维键/值数组 %# 'a=>foo, b=>bar'::hstore {{a,foo},{b,bar}}

Note: PostgreSQL 8.2之前,包含操作符@><@分别被称为@~。这些名字现在仍然可用,但是已经废弃了并且最终将会被移除。 请注意,旧的名字从大会移除,之前跟随着核心几何数据类型!

Table F-7. hstore 函数

函数 返回类型 描述 示例 结果
hstore(record) hstore 从一个记录或行构造一个 hstore hstore(ROW(1,2)) f1=>1,f2=>2
hstore(text[]) hstore 从一个数组构造一个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 从一个单独的键和值数组构造一个hstore hstore(ARRAY['a','b'], ARRAY['1','2']) "a"=>"1","b"=>"2"
hstore(text, text) hstore 制作单一项hstore hstore('a', 'b') "a"=>"b"
akeys(hstore) text[] 获取hstore的键作为一个数组 akeys('a=>1,b=>2') {a,b}
skeys(hstore) setof text 获取hstore的键作为一个集合 skeys('a=>1,b=>2') a b
avals(hstore) text[] 获取hstore的值作为一个数组 avals('a=>1,b=>2') {1,2}
svals(hstore) setof text 获取hstore的值作为一个集合 svals('a=>1,b=>2') 1 2
hstore_to_array(hstore) text[] 获取hstore的键和值作为一个键值交替的数组 hstore_to_array('a=>1,b=>2') {a,1,b,2}
hstore_to_matrix(hstore) text[] 获取hstore的键和值作为一个两维数组 hstore_to_matrix('a=>1,b=>2') {{a,1},{b,2}}
hstore_to_json(hstore) json 获取hstore作为一个json hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') {"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
hstore_to_json_loose(hstore) json 获取hstore作为一个json值,但是试图区分数值和布尔值,所以它们在JSON中没有引号 hstore_to_json_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') {"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}
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的键和值作为一个集合 select * from each('a=>1,b=>2') key => value: a => 1, b => 2
exist(hstore,text) boolean hstore 包含键吗? exist('a=>1','a') t
defined(hstore,text) boolean hstore 包含非NULL值的键吗? defined('a=>NULL','a') f
delete(hstore,text) hstore 删除匹配键的对 delete('a=>1,b=>2','b') "a"=>"1"
delete(hstore,text[]) hstore 删除匹配键的多个对 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 替换record中匹配hstore中的值的字段 参阅示例章节

Note:hstore值转换为json时使用hstore_to_json函数。

Note: 函数populate_record实际上是用anyelement, 而不是record,声明为它的第一个参数,但是它将用运行时错误拒绝非记录类型。

F.16.3. 索引

hstore有GiST 和 GIN索引支持@>, ?, ?&?|操作符。例如:

CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);

hstore也为=操作符支持btreehash索引。 这允许hstore字段声明为UNIQUE,或在GROUP BY, ORDER BYDISTINCT表达式中使用。为hstore 值的排序顺序不是很有用,但是这些索引可能对于等价查找有用处。为= 比较创建索引如下:

CREATE INDEX hidx ON testhstore USING BTREE (h);

CREATE INDEX hidx ON testhstore USING HASH (h);

F.16.4. 例子

添加一个键,或用新值更新一个现有的键:

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

删除一个键:

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

转换recordhstore:

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)

F.16.5. 统计

hstore类型,由于其内在的慷慨,可以包含大量不同的键。检查有效的键是应用的任务。 下列的例子演示几个检查键和获取统计的技术。

简单例子:

SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');

使用一个表:

SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;

在线统计:

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

F.16.6. 兼容性

自PostgreSQL 9.0起,hstore使用一个不同于以前版本的内部表示。 这样做对于转储/恢复升级没有什么障碍,因为文本表示(在转储中使用的)没有改变。

在一个二进制升级中,向上兼容是通过使新代码认识老格式的数据来维护的。 这在处理还未被新代码修改的数据时会有一点性能代偿。通过像下面这样的UPDATE 语句强制升级一个表字段中的所有值是可能的:

UPDATE tablename SET hstorecol = hstorecol || '';

另一个方法是:

ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';

ALTER TABLE方法要求在表上的一个排他锁,但是不会导致有旧行版本的表膨胀。

F.16.7. 作者

Oleg Bartunov <[[email protected]](mailto:[email protected])>, Moscow, Moscow University, Russia

Teodor Sigaev <[[email protected]](mailto:[email protected])>, Moscow, Delta-Soft Ltd., Russia

Andrew Gierth <[[email protected]](mailto:[email protected])>附加的增强, United Kingdom