PostgreSQL 教程

Appearance

F.18. hstore — hstore 键/值数据类型# ​

F.18.1. hstore 外部表示
F.18.2. hstore 运算符和函数
F.18.3. 索引
F.18.4. 示例
F.18.5. 统计信息
F.18.6. 兼容性
F.18.7. 转换
F.18.8. 作者

此模块为hstore数据类型实现，用于在单个PostgreSQL值中存储键/值对集。这在各种场景中都很有用，例如很少检查的具有许多属性的行或半结构化数据。键和值只是文本字符串。

此模块被认为是“受信任的”，也就是说，它可以由在当前数据库上具有CREATE权限的非超级用户安装。

F.18.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"

值（但不是键）可以是 SQLNULL。例如

key => NULL

NULL关键字不区分大小写。双引号NULL将其视为普通字符串“NULL”。

注意

请记住，在用于输入时，hstore文本格式在任何必需的引用或转义之前应用。如果您通过参数传递hstore文字，则不需要额外的处理。但是，如果您将其作为带引号的文字常量传递，则任何单引号字符和（取决于standard_conforming_strings配置参数的设置）反斜杠字符都需要正确转义。有关处理字符串常量的更多信息，请参见第 4.1.2.1 节。

在输出时，即使不是严格必需的，双引号始终包围键和值。

F.18.2.hstore运算符和函数# ​

由hstore模块提供的运算符显示在表 F.7中，函数显示在表 F.8中。

表 F.7.hstore运算符

运算符

说明

示例

hstore -> text → text

返回与给定键关联的值，如果不存在，则返回 NULL。

'a=>x, b=>y'::hstore -> 'a' → x

hstore -> text[] → text[]

返回与给定键关联的值，如果不存在，则返回 NULL。

'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a'] → {"z","x"}

hstore || hstore → hstore

连接两个 hstore。

'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore → "a"=>"b", "c"=>"x", "d"=>"q"

hstore ? text → boolean

hstore 是否包含键？

'a=>1'::hstore ? 'a' → t

hstore ?& text[] → boolean

hstore 是否包含所有指定键？

'a=>1,b=>2'::hstore ?& ARRAY['a','b'] → t

hstore ?| text[] → boolean

hstore 是否包含任何指定键？

'a=>1,b=>2'::hstore ?| ARRAY['b','c'] → t

hstore @> hstore → boolean

左操作数是否包含右操作数？

'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1' → t

hstore <@ hstore → boolean

左操作数是否包含在右操作数中？

'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL' → f

hstore - text → hstore

从左操作数中删除键。

'a=>1, b=>2, c=>3'::hstore - 'b'::text → "a"=>"1", "c"=>"3"

hstore - text[] → hstore

从左操作数中删除键。

'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b'] → "c"=>"3"

hstore - hstore → hstore

从左操作数中删除与右操作数中对匹配的对。

'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore → "a"=>"1", "c"=>"3"

anyelement #= hstore → anyelement

使用 hstore 中的匹配值替换左操作数（必须是复合类型）中的字段。

ROW(1,3) #= 'f1=>11'::hstore → (11,3)

%% hstore → text[]

将 hstore 转换为交替键和值的数组。

%% 'a=>foo, b=>bar'::hstore → {a,foo,b,bar}

%# hstore → text[]

将 hstore 转换为二维键/值数组。

%# 'a=>foo, b=>bar'::hstore → \{\{a,foo},{b,bar}}

表 F.8.hstore函数

函数

说明

示例

hstore ( record ) → hstore

从记录或行构造 hstore。

hstore(ROW(1,2)) → "f1"=>"1", "f2"=>"2"

hstore ( text[] ) → hstore

从数组构造 hstore，该数组可以是键/值数组或二维数组。

hstore(ARRAY['a','1','b','2']) → "a"=>"1", "b"=>"2"

hstore(ARRAY[['c','3'],['d','4']]) → "c"=>"3", "d"=>"4"

hstore ( text[], text[] ) → hstore

从单独的键和值数组构造 hstore。

hstore(ARRAY['a','b'], ARRAY['1','2']) → "a"=>"1", "b"=>"2"

hstore ( 文本, 文本 ) → hstore

生成一个单项 hstore。

hstore('a', 'b') → "a"=>"b"

akeys ( hstore ) → 文本[]

将 hstore 的键作为数组提取出来。

akeys('a=>1,b=>2') → {a,b}

skeys ( hstore ) → 文本的集合

将 hstore 的键作为集合提取出来。

skeys('a=>1,b=>2') →

a
b

avals ( hstore ) → 文本[]

将 hstore 的值作为数组提取出来。

avals('a=>1,b=>2') → {1,2}

svals ( hstore ) → 文本的集合

将 hstore 的值作为集合提取出来。

svals('a=>1,b=>2') →

1
2

hstore_to_array ( hstore ) → 文本[]

将 hstore 的键和值作为交替键和值的数组提取出来。

hstore_to_array('a=>1,b=>2') → {a,1,b,2}

hstore_to_matrix ( hstore ) → 文本[]

将 hstore 的键和值作为二维数组提取出来。

hstore_to_matrix('a=>1,b=>2') → \{\{a,1},{b,2}}

hstore_to_json ( hstore ) → json

将 hstore 转换为 json 值，并将所有非空值转换为 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_jsonb ( hstore ) → jsonb

将 hstore 转换为 jsonb 值，并将所有非空值转换为 JSON 字符串。

当 hstore 值转换为 jsonb 时，隐式使用此函数。

hstore_to_jsonb('"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}

hstore_to_jsonb_loose ( hstore ) → jsonb

将 hstore 转换为 jsonb 值，但尝试区分数字值和布尔值，以便在 JSON 中不加引号。

hstore_to_jsonb_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 record ( 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 ( anyelement, hstore ) → anyelement

使用 hstore 中的匹配值替换左操作数（必须是复合类型）中的字段。

populate_record(ROW(1,2), 'f1=>42'::hstore) → (42,2)

除了这些运算符和函数外，hstore类型的还可以作为下标，允许它们像关联数组一样工作。只能指定一个类型为text的下标；它被解释为一个键，并且获取或存储相应的值。例如，

CREATE TABLE mytable (h hstore);
INSERT INTO mytable VALUES ('a=>b, c=>d');
SELECT h['a'] FROM mytable;
 h
---
 b
(1 row)

UPDATE mytable SET h['c'] = 'new';
SELECT h FROM mytable;
          h
----------------------
 "a"=>"b", "c"=>"new"
(1 row)

如果下标为NULL或该键在hstore中不存在，则带下标的提取将返回NULL。（因此，带下标的提取与->运算符没有太大区别。）如果下标为NULL，则带下标的更新将失败；否则，它将替换该键的值，如果该键不存在，则向hstore中添加一个条目。

F.18.3. 索引# ​

hstore具有对@>、?、?&和?|运算符的 GiST 和 GIN 索引支持。例如

CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);

gist_hstore_opsGiST opclass 将一组键/值对近似为位图签名。其可选整数参数siglen确定签名长度（以字节为单位）。默认长度为 16 个字节。签名长度的有效值在 1 到 2024 字节之间。较长的签名会导致更精确的搜索（扫描更少部分的索引和更少的堆页），但代价是索引更大。

创建一个具有 32 字节签名长度的此类索引的示例

CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32));

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);

F.18.4. 示例# ​

添加键或使用新值更新现有键

UPDATE tab SET h['c'] = '3';

执行相同操作的另一种方法是

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

如果要在一次操作中添加或更改多个键，则连接方法比下标更有效

UPDATE tab SET h = h || hstore(array['q', 'w'], array['11', '12']);

删除键

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)

F.18.5. 统计信息# ​

由于其内在的宽容性，hstore类型可能包含许多不同的键。检查有效键是应用程序的任务。以下示例演示了几种用于检查键和获取统计信息的技巧。

简单示例

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

使用表

CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value 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.18.6. 兼容性# ​

从 PostgreSQL 9.0 开始，hstore使用与以前版本不同的内部表示形式。这不会对转储/恢复升级构成障碍，因为文本表示形式（在转储中使用）保持不变。

在二进制升级的情况下，通过让新代码识别旧格式数据来保持向上的兼容性。在处理尚未被新代码修改的数据时，这将导致轻微的性能损失。可以通过执行以下UPDATE语句来强制升级表列中的所有值

UPDATE tablename SET hstorecol = hstorecol || '';

另一种方法是

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

ALTER TABLE方法需要对表进行ACCESS EXCLUSIVE锁定，但不会导致表因旧行版本而膨胀。

F.18.7. 转换# ​

有其他扩展可用于为 PL/Perl 和 PL/Python 语言实现hstore类型的转换。PL/Perl 的扩展名为hstore_plperl和hstore_plperlu，分别用于受信任和不受信任的 PL/Perl。如果您安装这些转换并在创建函数时指定它们，hstore值将映射到 Perl 哈希。PL/Python 的扩展名为hstore_plpython3u。如果您使用它，hstore值将映射到 Python 字典。

注意 ​

强烈建议将转换扩展安装在与hstore相同的架构中。否则，如果转换扩展的架构包含恶意用户定义的对象，则在安装时存在安全隐患。

F.18.8. 作者# ​

Oleg Bartunov<[[email protected]](/cdn-cgi/l/email-protection#b7d8dbd2d0f7c4d6de99dac4c299c4c2)>，莫斯科，莫斯科大学，俄罗斯

Teodor Sigaev<[[email protected]](/cdn-cgi/l/email-protection#ccb8a9a3a8a3be8cbfa5abada9bae2beb9)>，莫斯科，Delta-Soft Ltd.，俄罗斯

英国安德鲁·吉尔特<[[email protected]](/cdn-cgi/l/email-protection#7b1a151f091e0c3b0f1a144a4a5509121f1f171e085514091c550e10)>的其他增强