hstore — тип данных hstore для пар ключ/значение
Этот модуль реализует тип данных hstore для хранения наборов пар ключ/значение внутри одного значения QHB. Это может быть полезно в различных сценариях, например, для строк со множеством редко проверяемых атрибутов или частично структурированных данных. Ключи и значения представляют собой простые текстовые строки.
Этот модуль считается «доверенным», то есть его могут устанавливать обычные пользователи с правом CREATE в текущей базе данных.
Внешнее представление hstore
Текстовое представление типа hstore, применяемое для ввода и вывода, включает ноль или более пар ключ => значение, разделенных запятыми. Несколько примеров:
k => v
foo => bar, baz => whatever
"1-a" => "anything at all"
Порядок пар не имеет значения (и может не воспроизводиться в выводе). Пробелы между парами и вокруг знака => игнорируются. Ключи и значения, содержащие пробелы, запятые и знаки = или >, нужно заключать в кавычки. Чтобы включить в ключ или значение обратный слэш или кавычку, следует экранировать их обратным слэшем.
Все ключи в hstore уникальны. Если объявить hstore с дублирующимися ключами, в нем будет сохранен только один ключ без гарантии выбора, какой именно:
SELECT 'a=>1,a=>2'::hstore;
hstore
----------
"a"=>"1"
Значением (но не ключом) может быть NULL SQL. Например:
key => NULL
Ключевое слово NULL нечувствительно к регистру. Чтобы NULL воспринимался как обычная строка «NULL», следует заключить его в кавычки.
Примечание
Учтите, что когда текстовый формат hstore используется для ввода данных, он применяется до всех требуемых кавычек или управляющих символов. Если вы передаете литерал hstore в параметре, в дополнительной обработке нет необходимости. Но если вы передаете его в виде строковой константы в кавычках, то все символы апострофов и (в зависимости от значения параметра конфигурации standard_conforming_strings) обратного слэша нужно корректно экранировать. Подробнее о работе со строковыми константами можно узнать в подразделе Строковые константы.
При выводе ключи и значения всегда заключаются в кавычки, даже если в этом нет особой необходимости.
Операторы и функции 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(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 ( ключ text, значение 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
...................
Авторы
Олег Бартунов (oleg@sai.msu.su), Москва, Московский государственный университет имени М. В. Ломоносова, Россия
Федор Сигаев (teodor@sigaev.ru), Москва, ООО «Дельта-Софт», Россия
Дополнительные улучшения внес Эндрю Гирт (Andrew Gierth) (andrew@tao11.riddles.org.uk), Великобритания