F.19. hstore
Этот модуль реализует тип данных 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_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. Авторы
Олег Бартунов <oleg@sai.msu.su>
, Москва, Московский Государственный Университет, Россия
Фёдор Сигаев <teodor@sigaev.ru>
, Москва, ООО «Дельта-Софт», Россия
Дополнительные улучшения внёс Эндрю Гирт <andrew@tao11.riddles.org.uk>
, Великобритания