Postgresql 中文操作指南

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

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

This module implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. This can be useful in various scenarios, such as rows with many attributes that are rarely examined, or semi-structured data. Keys and values are simply text strings.

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

This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.

F.18.1. hstore External Representation #

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

The text representation of an hstore, used for input and output, includes zero or more key value pairs separated by commas. Some examples:

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

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

The order of the pairs is not significant (and may not be reproduced on output). Whitespace between pairs or around the sign is ignored. Double-quote keys and values that include whitespace, commas, _=_s or _>_s. To include a double quote or a backslash in a key or value, escape it with a backslash.

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

Each key in an hstore is unique. If you declare an hstore with duplicate keys, only one will be stored in the hstore and there is no guarantee as to which will be kept:

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

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

A value (but not a key) can be an SQL NULL. For example:

key => NULL

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

The NULL keyword is case-insensitive. Double-quote the NULL to treat it as the ordinary string “NULL”.

Note

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

Keep in mind that the hstore text format, when used for input, applies before any required quoting or escaping. If you are passing an hstore literal via a parameter, then no additional processing is needed. But if you’re passing it as a quoted literal constant, then any single-quote characters and (depending on the setting of the standard_conforming_strings configuration parameter) backslash characters need to be escaped correctly. See Section 4.1.2.1 for more on the handling of string constants.

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

On output, double quotes always surround keys and values, even when it’s not strictly necessary.

F.18.2. hstore Operators and Functions #

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

The operators provided by the hstore module are shown in Table F.7, the functions in Table F.8.

Table F.7. hstore Operators

Table F.7. hstore Operators

Operator

Description

Example(s)

hstore texttext

Returns value associated with given key, or NULL if not present.

'a⇒x, b⇒y'::hstore → 'a'x

hstore text[]text[]

Returns values associated with given keys, or NULL if not present.

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

hstore _

_ hstorehstore

Concatenates two _hstore_s.

_'a⇒b, c⇒d'::hstore

'c⇒x, d⇒q'::hstore_ → "a"⇒"b", "c"⇒"x", "d"⇒"q"

hstore ? textboolean

Does hstore contain key?

'a⇒1'::hstore ? 'a't

hstore ?& text[]boolean

Does hstore contain all the specified keys?

'a⇒1,b⇒2'::hstore ?& ARRAY['a','b']t

hstore _?

_ text[]boolean

Does hstore contain any of the specified keys?

_'a⇒1,b⇒2'::hstore ?

ARRAY['b','c']_ → t

hstore @> hstoreboolean

Does left operand contain right?

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

hstore <@ hstoreboolean

Is left operand contained in right?

'a⇒c'::hstore <@ 'a⇒b, b⇒1, c⇒NULL'f

hstore - texthstore

Deletes key from left operand.

'a⇒1, b⇒2, c⇒3'::hstore - 'b'::text"a"⇒"1", "c"⇒"3"

hstore - text[]hstore

Deletes keys from left operand.

'a⇒1, b⇒2, c⇒3'::hstore - ARRAY['a','b']"c"⇒"3"

hstore - hstorehstore

Deletes pairs from left operand that match pairs in the right operand.

'a⇒1, b⇒2, c⇒3'::hstore - 'a⇒4, b⇒2'::hstore"a"⇒"1", "c"⇒"3"

anyelement #= hstoreanyelement

Replaces fields in the left operand (which must be a composite type) with matching values from hstore.

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

%% hstoretext[]

Converts hstore to an array of alternating keys and values.

%% 'a⇒foo, b⇒bar'::hstore{a,foo,b,bar}

% hstoretext[]

Converts hstore to a two-dimensional key/value array.

% 'a⇒foo, b⇒bar'::hstore{{a,foo},{b,bar}}

Table F.8. hstore Functions

Table F.8. hstore Functions

Function

Description

Example(s)

hstore ( record ) → hstore

Constructs an hstore from a record or row.

hstore(ROW(1,2))"f1"⇒"1", "f2"⇒"2"

hstore ( text[] ) → hstore

Constructs an hstore from an array, which may be either a key/value array, or a two-dimensional array.

hstore(ARRAY['a','1','b','2'])"a"⇒"1", "b"⇒"2"

hstore(ARRAY[['c','3'],['d','4']])"c"⇒"3", "d"⇒"4"

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

Constructs an hstore from separate key and value arrays.

hstore(ARRAY['a','b'], ARRAY['1','2'])"a"⇒"1", "b"⇒"2"

hstore ( text, text ) → hstore

Makes a single-item hstore.

hstore('a', 'b')"a"⇒"b"

akeys ( hstore ) → text[]

Extracts an hstore's keys as an array.

akeys('a⇒1,b⇒2'){a,b}

skeys ( hstore ) → setof text

Extracts an hstore's keys as a set.

skeys('a⇒1,b⇒2') → a b

avals ( hstore ) → text[]

Extracts an hstore's values as an array.

avals('a⇒1,b⇒2'){1,2}

svals ( hstore ) → setof text

Extracts an hstore's values as a set.

svals('a⇒1,b⇒2') → 1 2

hstore_to_array ( hstore ) → text[]

Extracts an hstore's keys and values as an array of alternating keys and values.

hstore_to_array('a⇒1,b⇒2'){a,1,b,2}

hstore_to_matrix ( hstore ) → text[]

Extracts an hstore's keys and values as a two-dimensional array.

hstore_to_matrix('a⇒1,b⇒2'){{a,1},{b,2}}

hstore_to_json ( hstore ) → json

Converts an hstore to a json value, converting all non-null values to JSON strings.

This function is used implicitly when an hstore value is cast to 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

Converts an hstore to a jsonb value, converting all non-null values to JSON strings.

This function is used implicitly when an hstore value is cast to 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

Converts an hstore to a json value, but attempts to distinguish numerical and Boolean values so they are unquoted in the 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

Converts an hstore to a jsonb value, but attempts to distinguish numerical and Boolean values so they are unquoted in the 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

Extracts a subset of an hstore containing only the specified keys.

slice('a⇒1,b⇒2,c⇒3'::hstore, ARRAY['b','c','x'])"b"⇒"2", "c"⇒"3"

each ( hstore ) → setof record ( key text, value text )

Extracts an hstore's keys and values as a set of records.

select * from each('a⇒1,b⇒2') → key

value -----+------- a

1 b

2

exist ( hstore, text ) → boolean

Does hstore contain key?

exist('a⇒1', 'a')t

defined ( hstore, text ) → boolean

Does hstore contain a non-NULL value for key?

defined('a⇒NULL', 'a')f

delete ( hstore, text ) → hstore

Deletes pair with matching key.

delete('a⇒1,b⇒2', 'b')"a"⇒"1"

delete ( hstore, text[] ) → hstore

Deletes pairs with matching keys.

delete('a⇒1,b⇒2,c⇒3', ARRAY['a','b'])"c"⇒"3"

delete ( hstore, hstore ) → hstore

Deletes pairs matching those in the second argument.

delete('a⇒1,b⇒2', 'a⇒4,b⇒2'::hstore)"a"⇒"1"

populate_record ( anyelement, hstore ) → anyelement

Replaces fields in the left operand (which must be a composite type) with matching values from hstore.

populate_record(ROW(1,2), 'f1⇒42'::hstore)(42,2)

除了这些操作数和函数之外,hstore 类型的变量值也可以作为下标,允许它们充当关联数组。只能指定一个 text 类型的下标;它被解释为一个键,并将获取或储存相应的值。例如:

In addition to these operators and functions, values of the hstore type can be subscripted, allowing them to act like associative arrays. Only a single subscript of type text can be specified; it is interpreted as a key and the corresponding value is fetched or stored. For example,

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 的输入。

A subscripted fetch returns NULL if the subscript is NULL or that key does not exist in the hstore. (Thus, a subscripted fetch is not greatly different from the operator.) A subscripted update fails if the subscript is NULL; otherwise, it replaces the value for that key, adding an entry to the hstore if the key does not already exist.

F.18.3. Indexes #

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

hstore has GiST and GIN index support for the @>, ?, ?& and ?| operators. For example:

CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);

gist_hstore_ops GiST 运算符类将一组键/值对近似为位图签名。它的可选择整数参数 siglen 以字节为单位确定签名长度。默认长度为 16 个字节。签名长度的有效值在 1 到 2024 字节之间。签名越长,搜索越精确(扫描索引中更小的部分和更少的堆页面),但索引越大。

gist_hstore_ops GiST opclass approximates a set of key/value pairs as a bitmap signature. Its optional integer parameter siglen determines the signature length in bytes. The default length is 16 bytes. Valid values of signature length are between 1 and 2024 bytes. Longer signatures lead to a more precise search (scanning a smaller fraction of the index and fewer heap pages), at the cost of a larger index.

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

Example of creating such an index with a signature length of 32 bytes:

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

hstore 还支持 btreehash= 运算符的索引。这允许将 hstore 列声明为 UNIQUE,或用于 GROUP BYORDER BYDISTINCT 表达式中。hstore 值的排序顺序并不是特别有用,但这些索引可能对等效查找有用。如以下所示为 = 比较创建索引:

hstore also supports btree or hash indexes for the = operator. This allows hstore columns to be declared UNIQUE, or to be used in GROUP BY, ORDER BY or DISTINCT expressions. The sort ordering for hstore values is not particularly useful, but these indexes may be useful for equivalence lookups. Create indexes for = comparisons as follows:

CREATE INDEX hidx ON testhstore USING BTREE (h);

CREATE INDEX hidx ON testhstore USING HASH (h);

F.18.4. Examples #

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

Add a key, or update an existing key with a new value:

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

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

Another way to do the same thing is:

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

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

If multiple keys are to be added or changed in one operation, the concatenation approach is more efficient than subscripting:

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

删除一个键:

Delete a key:

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

record 转换为 hstore

Convert a record to an 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 类型:

Convert an hstore to a predefined record type:

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 中的值修改现有记录:

Modify an existing record using the values from an 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 类型由于其内在的自由性,可能包含很多不同的键。检查有效键是应用程序的任务。以下示例演示了检查键和获取统计信息的几种技术。

The hstore type, because of its intrinsic liberality, could contain a lot of different keys. Checking for valid keys is the task of the application. The following examples demonstrate several techniques for checking keys and obtaining statistics.

简单示例:

Simple example:

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

使用表:

Using a table:

CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value FROM testhstore;

在线统计:

Online statistics:

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 使用不同于先前版本的内部表示形式。由于文本表示形式(在转储中使用)保持不变,因此这对转储/恢复升级不会造成任何障碍。

As of PostgreSQL 9.0, hstore uses a different internal representation than previous versions. This presents no obstacle for dump/restore upgrades since the text representation (used in the dump) is unchanged.

在进行二进制升级时,新代码识别旧格式数据,从而保持向上兼容性。在处理尚未修改数据之前,这将导致稍许的性能损失。可以执行 UPDATE 语句,强制升级表列中的所有值,如下所示:

In the event of a binary upgrade, upward compatibility is maintained by having the new code recognize old-format data. This will entail a slight performance penalty when processing data that has not yet been modified by the new code. It is possible to force an upgrade of all values in a table column by doing an UPDATE statement as follows:

UPDATE tablename SET hstorecol = hstorecol || '';

另一种方法是:

Another way to do it is:

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

ALTER TABLE 方法需要对表加上 ACCESS EXCLUSIVE 锁,但不会导致表中出现旧行版本而变得内容膨胀。

The ALTER TABLE method requires an ACCESS EXCLUSIVE lock on the table, but does not result in bloating the table with old row versions.

F.18.7. Transforms #

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

Additional extensions are available that implement transforms for the hstore type for the languages PL/Perl and PL/Python. The extensions for PL/Perl are called hstore_plperl and hstore_plperlu, for trusted and untrusted PL/Perl. If you install these transforms and specify them when creating a function, hstore values are mapped to Perl hashes. The extension for PL/Python is called hstore_plpython3u. If you use it, hstore values are mapped to Python dictionaries.

Caution

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

It is strongly recommended that the transform extensions be installed in the same schema as hstore. Otherwise there are installation-time security hazards if a transform extension’s schema contains objects defined by a hostile user.

F.18.8. Authors #

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

Oleg Bartunov <link:mailto:oleg@sai.msu.su[oleg@sai.msu.su]>, Moscow, Moscow University, Russia

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

Teodor Sigaev <link:mailto:teodor@sigaev.ru[teodor@sigaev.ru]>, Moscow, Delta-Soft Ltd., Russia

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

Additional enhancements by Andrew Gierth <link:mailto:andrew@tao11.riddles.org.uk[andrew@tao11.riddles.org.uk]>, United Kingdom