Postgresql 中文操作指南

F.18. hstore — hstore key/value datatype #

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

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

F.18.1. hstore External Representation #

hstore 的文本表示（用于输入和输出）包括用逗号分隔的零个或多个 key ⇒ value 对。一些示例：

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

键值对的顺序不重要（并且可能不会在输出中重现）。将忽略键值对之间的或_⇒符号周围的空白。对包含空白、逗号、=_s 或 _>_s 的双引号键和值进行转义。要将双引号或反斜杠包含在键或值中，请用反斜杠对其进行转义。

hstore 中的每一个键都是唯一的。如果您声明具有重复键的 hstore，则只会将其中一个存储在 hstore 中，并且无法保证会保留哪一个：

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

一个值（但不是一个键）可以是一个 SQL NULL。例如：

key => NULL

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

Note

请记住，当用作输入时，_hstore_文本格式会应用_before_任何必需的引号或转义。如果您要通过参数传递_hstore_文本，则不需要额外的处理。但是，如果您要将其作为带引号的文本常量传递，则需要正确转义所有单引号字符以及（取决于_standard_conforming_strings_配置参数的设置）反斜杠字符。有关处理字符串常量的详细信息，请参阅 Section 4.1.2.1。

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

F.18.2. hstore Operators and Functions #

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

Table F.7. hstore Operators

Operator

Description

Example(s)

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_s.'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}}

Table F.8. hstore Functions

Function

Description

Example(s)

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 ( 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') →ab

avals ( hstore ) → text[] 提取 hstore 的值作为数组。 avals('a⇒1,b⇒2') → {1,2}

svals ( hstore ) → setof text 提取 hstore 的值作为集合。 svals('a⇒1,b⇒2') →12

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 值，将所有非空值转换为 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') →键

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

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

CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);

gist_hstore_ops GiST 运算符类将一组键/值对近似为位图签名。它的可选择整数参数 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. Examples #

添加一个键或使用一个新值更新一个现有键：

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

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

从 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. Transforms #

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

Caution

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

F.18.8. Authors #

Oleg Bartunov < link:mailto:oleg@sai.msu.su[oleg@sai.msu.su]> ，莫斯科，莫斯科大学，俄罗斯

Teodor Sigaev < link:mailto:teodor@sigaev.ru[teodor@sigaev.ru]> ，莫斯科，Delta-Soft Ltd.，俄罗斯

Andrew Gierth < link:mailto:andrew@tao11.riddles.org.uk[andrew@tao11.riddles.org.uk]> ，英国提供的其他增强功能