E.16. hstore

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

E.16.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.

При выводе значения и ключи всегда заключаются в кавычки, даже когда без этого можно обойтись.

E.16.2. Операторы и функции hstore

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

Таблица E-6. Операторы 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 операторы включения @> и <@ обозначались соответственно как @ и ~. Эти имена по-прежнему действуют, но считаются устаревшими и в конце концов будут упразднены. Заметьте, что старые имена произошли из соглашения, которому раньше следовали геометрические типы данных!

Таблица E-7. Функции 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 в виде значения 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_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}
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.

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

E.16.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);

E.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)

E.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
...................

E.16.6. Совместимость

Начиная с PostgreSQL 9.0, hstore использует внутреннее представление, отличающееся от предыдущих версий. Это не проблема при обновлении путём выгрузки/перезагрузки данных, так как текстовое представление (используемое при выгрузке) не меняется.

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

UPDATE tablename SET hstorecol = hstorecol || '';

Это можно сделать и так:

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

Вариант с командой ALTER TABLE требует расширенной блокировки таблицы, но не приводит к замусориванию таблицы старыми версиями строк.

E.16.7. Авторы

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

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

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