F.23. hstore

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

Данный модуль считается «доверенным», то есть его могут устанавливать обычные пользователи, имеющие право CREATE в текущей базе данных.

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

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

Таблица F.17. Операторы hstore

Оператор

Описание

Примеры

hstore -> texttext

Выдаёт значение для заданного ключа, или 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 || hstorehstore

Соединяет два набора hstore.

'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore"a"=>"b", "c"=>"x", "d"=>"q"

hstore ? textboolean

Набор 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 @> hstoreboolean

Левый операнд содержит правый?

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

hstore <@ hstoreboolean

Левый операнд содержится в правом?

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

hstore - texthstore

Удаляет ключ из левого операнда.

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

Удаляет из левого операнда пары ключ-значение, совпадающие с парами в правом.

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

anyelement #= hstoreanyelement

Заменяет поля в левом операнде, имеющем составной тип, соответствующими значениями из hstore.

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

%% hstoretext[]

Преобразует hstore в массив перемежающихся ключей и значений.

%% 'a=>foo, b=>bar'::hstore{a,foo,b,bar}

%# hstoretext[]

Преобразует hstore в двумерный массив ключей-значений.

%# 'a=>foo, b=>bar'::hstore{{a,foo},{b,bar}}


Таблица F.18. Функции 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, переводя все отличные от NULL значения в строки 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, переводя все отличные от NULL значения в строки 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')

 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 ( 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, если индекс — NULL или указанный ключ не существует в hstore. (Таким образом, обращение по индексу не сильно отличается от оператора ->.) Изменение по индексу не выполняется, если индекс — NULL; в остальных случаях заменяется значение указанного ключа (если такой ключ не существует, он добавляется в hstore).

F.23.3. Индексы

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

CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);

Класс операторов GiST gist_hstore_ops аппроксимирует набор пар ключ-значение в виде сигнатуры битовой карты. В его необязательном целочисленном параметре siglen можно задать размер сигнатуры в байтах. Параметр может принимать значения от 1 до 2024, по умолчанию он равен 16. При увеличении размера сигнатуры поиск работает точнее (сканируется меньшая область в индексе и меньше страниц кучи), но сам индекс становится больше.

Пример создания такого индекса с длиной сигнатуры 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.23.4. Примеры

Присваивание значения существующему или новому ключу:

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.23.5. Статистика

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

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

Внимание

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

F.23.8. Авторы

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

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

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