hstore — тип данных «ключ-значение» (hstore)
Эта страница переведена при помощи нейросети GigaChat.
Этот модуль реализует тип данных 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 передается литерал через параметр, то дополнительная обработка не требуется. Но если передается как константа с кавычками, то любые символы одинарной кавычки и (в зависимости от настройки параметра конфигурации standard_conforming_strings) символы обратной косой черты должны быть правильно экранированы.
При выводе двойные кавычки всегда окружают ключи и значения, даже когда это не обязательно.
Операторы и функции hstore
Операторы, предоставляемые модулем, показаны в таблице «Операторы hstore», функции в таблице «Функции hstore».
Операторы hstore:
| Оператор | Описание | Пример(ы) |
|---|---|---|
hstore -> text → text | Возвращает значение, связанное с данным ключом, или 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 || hstore → hstore | Конкатенирует два hstore | 'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore → "a"=>"b", "c"=>"x", "d"=>"q" |
hstore ? text → boolean | Содержит ли 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 @> hstore → boolean | Содержит ли левый операнд правый? | 'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1' → t |
hstore <@ hstore → boolean | Содержится ли левый операнд в правом? | 'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL' → f |
hstore - text → hstore | Удаляет ключ из левого операнда | '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 - hstore → hstore | Удаляет пары из левого операнда, которые совпадают с парами в правом операнде | 'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore → "a"=>"1", "c"=>"3" |
anyelement #= hstore → anyelement | Заменяет поля в левом операнде (который должен быть составным типом) соответствующими значениями из hstore | ROW(1,3) #= 'f1=>11'::hstore → (11,3) |
%% hstore → text[] | Преобразует hstore в массив чередующихся ключей и значений | %% 'a=>foo, b=>bar'::hstore → {a,foo,b,bar} |
%# hstore → text[] | Преобразует hstore в двухмерный массив ключ/значение | %# 'a=>foo, b=>bar'::hstore → {{a,foo},{b,bar}} |
Функции 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 ( 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, преобразуя все ненулевые значения в строки JSON | Эта функция используется неявно при приведении значения hstore к типу json |
hstore_to_jsonb ( hstore ) → jsonb | Преобразует значение hstore в значение jsonb, преобразуя все ненулевые значения в строки JSON | Эта функция используется неявно при приведении значения hstore к типу jsonb |
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, если ключ еще не существует.
Индексы
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.
Рекомендуется устанавливать расширения преобразования в той же схеме, что и hstore. В противном случае возникают угрозы безопасности во время установки, если схема расширения преобразования содержит объекты, определенные враждебным пользователем.