F.19. hstore
Пред. НаверхПриложение F. Дополнительно поставляемые модулиНачало След.

F.19. hstore

F.19.1. Внешнее представление hstore
F.19.2. Операторы и функции hstore
F.19.3. Индексы
F.19.4. Примеры
F.19.5. Статистика
F.19.6. Совместимость
F.19.7. Трансформации
F.19.8. Авторы

Этот модуль реализует тип данных hstore для хранения пар ключ-значение внутри одного значения Postgres Pro. Это может быть полезно в самых разных сценариях, например для хранения строк со множеством редко анализируемых атрибутов или частично структурированных данных. Ключи и значения задаются простыми текстовыми строками.

F.19.1. Внешнее представление hstore

Текстовое представление типа hstore, применяемое для ввода и вывода, включает ноль или более пар ключ => значение, разделённых запятыми. Несколько примеров: 

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.19.2. Операторы и функции hstore

Реализованные в модуле hstore операторы перечислены в Таблице F.7, функции — в Таблице F.8.

Таблица F.7. Операторы 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заменяет поля в record соответствующими значениями из hstoreсм. раздел Примеры 
%% hstoreпреобразует hstore в массив перемежающихся ключей и значений%% 'a=>foo, b=>bar'::hstore{a,foo,b,bar}
%# hstoreпреобразует 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']) || 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, преобразуя все отличные от NULL значения в строки JSONhstore_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, преобразуя все отличные от NULL значения в строки JSONhstore_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извлекает подмножество из hstoreslice('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см. раздел Примеры 

Примечание

Функция hstore_to_json применяется, когда значение hstore нужно привести к json. Подобным образом, hstore_to_jsonb применяется, когда значение hstore нужно привести к jsonb.

Примечание

Функция populate_record на самом деле объявлена как принимающая в первом аргументе anyelement, а не record, но если ей будет передан не тип записи, она выдаст ошибку.

F.19.3. Индексы

Тип hstore поддерживает индексы GiST и GIN для операторов @>, ?, ?& и ?|. Например:

CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);

Тип 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.19.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.19.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.19.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.19.7. Трансформации

Также имеются дополнительные расширения, реализующие трансформации типа hstore для языков PL/Perl и PL/Python. Расширения для PL/Perl называются hstore_plperl и hstore_plperlu для доверенного и недоверенного PL/Perl, соответственно. Если вы установите эти трансформации и укажете их при создании функции, значения hstore будут отображаться в хеши Perl. Расширения для PL/Python называются hstore_plpythonu, hstore_plpython2u и hstore_plpython3u (соглашения об именовании, принятые для интерфейса PL/Python, описаны в Разделе 44.1). Если вы воспользуетесь ими, значения hstore будут отображаться в словари Python.

Внимание

Расширения, реализующие трансформации, настоятельно рекомендуется устанавливать в одну схему с hstore. Выбор какой-либо другой схемы, которая может содержать объекты, созданные злонамеренным пользователем, чреват угрозами безопасности во время установки расширения.

F.19.8. Авторы

Олег Бартунов , Москва, Московский Государственный Университет, Россия

Фёдор Сигаев , Москва, ООО «Дельта-Софт», Россия

Дополнительные улучшения внёс Эндрю Гирт , Великобритания

Пред. Наверх След.
F.18. fuzzystrmatch Начало F.20. Модули словарей Hunspell
9.19. Array Functions and Operators
Prev UpChapter 9. Functions and OperatorsHome Next

9.19. Array Functions and Operators #

Table 9.54 shows the specialized operators available for array types. In addition to those, the usual comparison operators shown in Table 9.1 are available for arrays. The comparison operators compare the array contents element-by-element, using the default B-tree comparison function for the element data type, and sort based on the first difference. In multidimensional arrays the elements are visited in row-major order (last subscript varies most rapidly). If the contents of two arrays are equal but the dimensionality is different, the first difference in the dimensionality information determines the sort order.

Table 9.54. Array Operators

Operator

Description

Example(s)

anyarray @> anyarray → boolean

Does the first array contain the second, that is, does each element appearing in the second array equal some element of the first array? (Duplicates are not treated specially, thus ARRAY[1] and ARRAY[1,1] are each considered to contain the other.)

ARRAY[1,4,3] @> ARRAY[3,1,3] → t

anyarray <@ anyarray → boolean

Is the first array contained by the second?

ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6] → t

anyarray && anyarray → boolean

Do the arrays overlap, that is, have any elements in common?

ARRAY[1,4,3] && ARRAY[2,1] → t

anycompatiblearray || anycompatiblearray → anycompatiblearray

Concatenates the two arrays. Concatenating a null or empty array is a no-op; otherwise the arrays must have the same number of dimensions (as illustrated by the first example) or differ in number of dimensions by one (as illustrated by the second). If the arrays are not of identical element types, they will be coerced to a common type (see Section 10.5).

ARRAY[1,2,3] || ARRAY[4,5,6,7] → {1,2,3,4,5,6,7}

ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9.9]] → {{1,2,3},{4,5,6},{7,8,9.9}}

anycompatible || anycompatiblearray → anycompatiblearray

Concatenates an element onto the front of an array (which must be empty or one-dimensional).

3 || ARRAY[4,5,6] → {3,4,5,6}

anycompatiblearray || anycompatible → anycompatiblearray

Concatenates an element onto the end of an array (which must be empty or one-dimensional).

ARRAY[4,5,6] || 7 → {4,5,6,7}


See Section 8.15 for more details about array operator behavior. See Section 11.2 for more details about which operators support indexed operations.

Table 9.55 shows the functions available for use with array types. See Section 8.15 for more information and examples of the use of these functions.

Table 9.55. Array Functions

Function

Description

Example(s)

array_append ( anycompatiblearray, anycompatible ) → anycompatiblearray

Appends an element to the end of an array (same as the anycompatiblearray || anycompatible operator).

array_append(ARRAY[1,2], 3) → {1,2,3}

array_cat ( anycompatiblearray, anycompatiblearray ) → anycompatiblearray

Concatenates two arrays (same as the anycompatiblearray || anycompatiblearray operator).

array_cat(ARRAY[1,2,3], ARRAY[4,5]) → {1,2,3,4,5}

array_dims ( anyarray ) → text

Returns a text representation of the array's dimensions.

array_dims(ARRAY[[1,2,3], [4,5,6]]) → [1:2][1:3]

array_fill ( anyelement, integer[] [, integer[] ] ) → anyarray

Returns an array filled with copies of the given value, having dimensions of the lengths specified by the second argument. The optional third argument supplies lower-bound values for each dimension (which default to all 1).

array_fill(11, ARRAY[2,3]) → {{11,11,11},{11,11,11}}

array_fill(7, ARRAY[3], ARRAY[2]) → [2:4]={7,7,7}

array_length ( anyarray, integer ) → integer

Returns the length of the requested array dimension. (Produces NULL instead of 0 for empty or missing array dimensions.)

array_length(array[1,2,3], 1) → 3

array_length(array[]::int[], 1) → NULL

array_length(array['text'], 2) → NULL

array_lower ( anyarray, integer ) → integer

Returns the lower bound of the requested array dimension.

array_lower('[0:2]={1,2,3}'::integer[], 1) → 0

array_ndims ( anyarray ) → integer

Returns the number of dimensions of the array.

array_ndims(ARRAY[[1,2,3], [4,5,6]]) → 2

array_position ( anycompatiblearray, anycompatible [, integer ] ) → integer

Returns the subscript of the first occurrence of the second argument in the array, or NULL if it's not present. If the third argument is given, the search begins at that subscript. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to search for NULL.

array_position(ARRAY['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'], 'mon') → 2

array_positions ( anycompatiblearray, anycompatible ) → integer[]

Returns an array of the subscripts of all occurrences of the second argument in the array given as first argument. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to search for NULL. NULL is returned only if the array is NULL; if the value is not found in the array, an empty array is returned.

array_positions(ARRAY['A','A','B','A'], 'A') → {1,2,4}

array_prepend ( anycompatible, anycompatiblearray ) → anycompatiblearray

Prepends an element to the beginning of an array (same as the anycompatible || anycompatiblearray operator).

array_prepend(1, ARRAY[2,3]) → {1,2,3}

array_remove ( anycompatiblearray, anycompatible ) → anycompatiblearray

Removes all elements equal to the given value from the array. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to remove NULLs.

array_remove(ARRAY[1,2,3,2], 2) → {1,3}

array_replace ( anycompatiblearray, anycompatible, anycompatible ) → anycompatiblearray

Replaces each array element equal to the second argument with the third argument.

array_replace(ARRAY[1,2,5,4], 5, 3) → {1,2,3,4}

array_sample ( array anyarray, n integer ) → anyarray

Returns an array of n items randomly selected from array. n may not exceed the length of array's first dimension. If array is multi-dimensional, an “item” is a slice having a given first subscript.

array_sample(ARRAY[1,2,3,4,5,6], 3) → {2,6,1}

array_sample(ARRAY[[1,2],[3,4],[5,6]], 2) → {{5,6},{1,2}}

array_shuffle ( anyarray ) → anyarray

Randomly shuffles the first dimension of the array.

array_shuffle(ARRAY[[1,2],[3,4],[5,6]]) → {{5,6},{1,2},{3,4}}

array_to_string ( array anyarray, delimiter text [, null_string text ] ) → text

Converts each array element to its text representation, and concatenates those separated by the delimiter string. If null_string is given and is not NULL, then NULL array entries are represented by that string; otherwise, they are omitted. See also string_to_array.

array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') → 1,2,3,*,5

array_upper ( anyarray, integer ) → integer

Returns the upper bound of the requested array dimension.

array_upper(ARRAY[1,8,3,7], 1) → 4

cardinality ( anyarray ) → integer

Returns the total number of elements in the array, or 0 if the array is empty.

cardinality(ARRAY[[1,2],[3,4]]) → 4

trim_array ( array anyarray, n integer ) → anyarray

Trims an array by removing the last n elements. If the array is multidimensional, only the first dimension is trimmed.

trim_array(ARRAY[1,2,3,4,5,6], 2) → {1,2,3,4}

unnest ( anyarray ) → setof anyelement

Expands an array into a set of rows. The array's elements are read out in storage order.

unnest(ARRAY[1,2]) → 

 1
 2

unnest(ARRAY[['foo','bar'],['baz','quux']]) → 

 foo
 bar
 baz
 quux

unnest ( anyarray, anyarray [, ... ] ) → setof anyelement, anyelement [, ... ]

Expands multiple arrays (possibly of different data types) into a set of rows. If the arrays are not all the same length then the shorter ones are padded with NULLs. This form is only allowed in a query's FROM clause; see Section 7.2.1.4.

select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b) → 

 a |  b
---+-----
 1 | foo
 2 | bar
   | baz


See also Section 9.21 about the aggregate function array_agg for use with arrays.

Prev Up Next
9.18. Conditional Expressions Home 9.20. Range/Multirange Functions and Operators