PostgreSQL 13.1 中文入门教程 PostgreSQL hstore

2024-02-25 开发教程 PostgreSQL 13.1 中文入门教程匿名 0℃

F.16.1. hstore外部表示
F.16.2. hstore操作符和函数
F.16.3. 索引
F.16.4. 例子
F.16.5. 统计
F.16.6. 兼容性
F.16.7. 转换

这个模块实现了hstore数据类型用来在一个单一PostgreSQL值中存储键值对。这在很多情景下都有用，例如带有很多很少被检查的属性的行或者半结构化数据。键和值都是简单的文本字符串。

这个模块被视为“trusted”，也就是说，它可以由对当前数据库具有CREATE权限的非超级用户安装。

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”。

注意

记住当hstore文本格式当被用于输入时，它应用在任何必须的引用或转义之前。如果你通过一个参数传递一个hstore文字，那么不需要额外的处理。但是如果你将它作为一个引用的文字常数，那么任何单引号字符以及（取决于standard_conforming_strings配置参数的设置）反斜线字符需要被正确地转义。更多关于处理字符串常量的处理可见第 4.1.2.1 节。

在输出时，双引号总是围绕着键和值，即使这样做不是绝对必要。

F.16.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` 从左操作符中删除与右操作符中的对(pairs)相匹配的对。 `'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}}`

操作符

描述

例子

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

从左操作符中删除与右操作符中的对(pairs)相匹配的对。

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

注意

在 PostgreSQL 8.2 之前，包含操作符@>和<@分别被称为@和~。这些名称仍然可用，但是已经被弃用并且最终将被移除。注意，旧名称和原来核心几何数据类型所遵循的习惯是相反的！

表 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`( `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`值，将所有非空值转换为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`( `keytext`, `valuetext`) 提取`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` 删除带有匹配键的对(pair)。 `delete('a=>1,b=>2', 'b')`→ `"a"=>"1"`
`delete`( `hstore`, `text[]`) → `hstore` 删除带有匹配键的对(pairs)。 `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( 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')→

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值，将所有非空值转换为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( keytext, valuetext)

提取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

删除带有匹配键的对(pair)。

delete('a=>1,b=>2', 'b')→ "a"=>"1"

delete( hstore, text[]) → hstore

删除带有匹配键的对(pairs)。

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)

F.16.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.16.4. 例子

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

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)

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

有一些额外的扩展为语言 PL/Perl 和 PL/Python 实现了hstore类型的转换。用于 PL/Perl 的扩展叫做hstore_plperl和 hstore_plperlu，分别用于可信的和不可信的 PL/Perl。如果安装这些转换并且在创建函数时指定它们，hstore值会被映射成 Perl 哈希。用于 PL/Python 的扩展是hstore_plpythonu、 hstore_plpython2u和hstore_plpython3u（ PL/Python 命名习惯见第 45.1 节）。如果使用它们， hstore值会被映射成 Python 字典。

小心

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

上一节： PostgreSQL fuzzystrmatch

下一节： PostgreSQL intagg

"PostgreSQL 13.1 中文入门教程"的目录