F.16. hstore
Этот модуль реализует тип данных hstore
для хранения пар ключ-значение внутри одного значения PostgreSQL. Это может быть полезно в самых разных сценариях, например для хранения строк со множеством редко анализируемых атрибутов или частично структурированных данных. Ключи и значения задаются простыми текстовыми строками.
Данный модуль считается «доверенным», то есть его могут устанавливать обычные пользователи, имеющие право CREATE
в текущей базе данных.
F.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.
При выводе значения и ключи всегда заключаются в кавычки, даже когда без этого можно обойтись.
F.16.2. Операторы и функции hstore
Реализованные в модуле hstore
операторы перечислены в Таблице F.7, функции — в Таблице F.8.
Таблица F.7. Операторы hstore
Оператор Описание Примеры |
---|
Выдаёт значение для заданного ключа, или
|
Выдаёт значения, связанные с заданными ключами, или
|
Соединяет два набора
|
Набор
|
Набор
|
Набор
|
Левый операнд содержит правый?
|
Левый операнд содержится в правом?
|
Удаляет ключ из левого операнда.
|
Удаляет ключи из левого операнда.
|
Удаляет из левого операнда пары ключ-значение, совпадающие с парами в правом.
|
Заменяет поля в левом операнде, имеющем составной тип, соответствующими значениями из
|
Преобразует
|
Преобразует
|
Примечание
До версии PostgreSQL 8.2 операторы включения @>
и <@
обозначались соответственно как @
и ~
. Эти имена по-прежнему действуют, но считаются устаревшими и в конце концов будут упразднены. Заметьте, что старые имена произошли из соглашения, которому раньше следовали геометрические типы данных!
Таблица F.8. Функции hstore
F.16.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.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)
F.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 ...................
F.16.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.16.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, описаны в Разделе 45.1). Если вы воспользуетесь ими, значения hstore
будут отображаться в словари Python.
Внимание
Расширения, реализующие трансформации, настоятельно рекомендуется устанавливать в одну схему с hstore
. Выбор какой-либо другой схемы, которая может содержать объекты, созданные злонамеренным пользователем, чреват угрозами безопасности во время установки расширения.
F.16.8. Авторы
Олег Бартунов <oleg@sai.msu.su>
, Москва, Московский Государственный Университет, Россия
Фёдор Сигаев <teodor@sigaev.ru>
, Москва, ООО «Дельта-Софт», Россия
Дополнительные улучшения внёс Эндрю Гирт <andrew@tao11.riddles.org.uk>
, Великобритания