hstore — тип данных «ключ-значение» (hstore)#

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

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

Внешнее представление 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», функции в таблице «Функции hstore».

Операторы hstore:

Функции hstore:

В дополнение к этим операторам и функциям значения типа 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, если ключ еще не существует.

Индексы#

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

CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);

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

Пример создания такого индекса с длиной подписи 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);

Примеры#

Добавление ключа или обновление существующего ключа новым значением:

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)

Статистика#

Тип 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
...................

Совместимость#

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

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

UPDATE tablename SET hstorecol = hstorecol || '';

Еще один способ сделать это:

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

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

Преобразования#

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