Тип данных hstore

Евгения Кузина

В PostgreSQL тип данных hstore позволяет хранить пары ключ/значение в одном значении поля. Тип hstore используется, когда ключи не могут быть определены заранее или отличаются для разных объектов.

Тип данных hstore реализуется модулем hstore. Чтобы использовать этот тип, нужно создать расширение hstore:

CREATE EXTENSION hstore;

Значение hstore содержит пары ключ => значение, разделенные запятыми. Ключи и значения хранятся в виде текстовых строк.

Создадим таблицу products со столбцом hstore, чтобы посмотреть, как работать с этим типом:

CREATE TABLE products (
  id serial PRIMARY KEY,
  name character varying(100),
  attributes hstore
);

В этой таблице разные продукты могут иметь разные атрибуты. Добавим в таблицу следующие строки:

INSERT INTO products (name, attributes) VALUES (
 'The Time Machine',
 'author => "Herbert Wells",
  pages => 202,
  genre => "sci-fi"'),
('Hyperion',
 'author => "Dan Simmons",
  pages => 640,
  genre => "sci-fi"'),
('The Great Gatsby',
'author => "F. Scott Fitzgerald",
  pages => 256,
  genre => "novel"'),
('People',
 'publisher => "Time Inc",
  number => 38,
  date => "09.12.2022"'
);

Содержание таблицы:

 id |       name       |                    attributes
----+------------------+-----------------------------------------------------------------
  1 | The Time Machine | "genre"=>"sci-fi", "pages"=>"202", "author"=>"Herbert Wells"
  2 | Hyperion         | "genre"=>"sci-fi", "pages"=>"640", "author"=>"Dan Simmons"
  3 | The Great Gatsby | "genre"=>"novel", "pages"=>"256", "author"=>"F. Scott Fitzgerald"
  4 | People           | "date"=>"09.12.2022", "number"=>"38", "publisher"=>"Time Inc"

Тип hstore имеет следующие особенности синтаксиса:

  • Порядок пар hstore не имеет значения и может быть не воспроизведен на выходе.

  • PostgreSQL игнорирует пробелы между парами или вокруг знака =>. Ключи и значения, содержащие пробелы, запятые, знаки = или >, должны быть заключены в двойные кавычки. Чтобы использовать двойную кавычку (") или обратную косую черту (\) в ключе или значении, заэкранируйте их обратной косой чертой (\", \\).

  • Каждый ключ должен быть уникальным.

  • Значение может содержать SQL NULL (key => NULL). Ключевое слово null не зависит от регистра. Заключите его в двойные кавычки, чтобы обрабатывать как строковое значение "NULL".

Операции со значениями hstore

PostgreSQL предоставляет большое количество операторов и функций для работы с типом hstore. В этом разделе приведены примеры типовых операций, которые можно выполнять с помощью этого функционала. Для получения дополнительной информации об операторах и функциях hstore обратитесь к документации PostgreSQL: Hstore operators and functions.

Получение значения по ключу

Вы можете использовать оператор ->, чтобы получить значение определенного ключа из столбца hstore. Если ключ отсутствует, соответствующая строка не включается в результат:

SELECT name, attributes->'author' as author
FROM products;

Результат:

       name       |       author
------------------+---------------------
 The Time Machine | Herbert Wells
 Hyperion         | Dan Simmons
 The Great Gatsby | F. Scott Fitzgerald

Вы также можете использовать оператор -> в выражении WHERE. Следующий запрос выбирает продукты, для которых указан жанр sci-fi:

SELECT name, attributes->'author' as author
FROM products
WHERE attributes -> 'genre' = 'sci-fi';

Результат:

       name       |    author
------------------+---------------
 The Time Machine | Herbert Wells
 Hyperion         | Dan Simmons

Добавление пары ключ/значение к существующему значению hstore

Вы можете добавить новую пару ключ/значение в существующие строки. Для этого используйте оператор ||, который объединяет два значения hstore. Следующий запрос добавляет "freeshipping"⇒"yes" ко всем существующим строкам:

UPDATE products
SET attributes = attributes || '"freeshipping"=>"yes"' :: hstore;

Изменение пары ключ/значение

Чтобы обновить существующую пару ключ/значение, используйте тот же оператор ||. Следующий запрос изменяет значение freeshipping книги Hyperion на no:

UPDATE products
SET attributes = attributes || '"freeshipping"=>"no"' :: hstore
WHERE name = 'Hyperion';

Содержание таблицы:

 id |       name       |                                        attributes
----+------------------+------------------------------------------------------------------------------------------
  1 | The Time Machine | "genre"=>"sci-fi", "pages"=>"202", "author"=>"Herbert Wells", "freeshipping"=>"yes"
  2 | Hyperion         | "genre"=>"sci-fi", "pages"=>"640", "author"=>"Dan Simmons", "freeshipping"=>"no"
  3 | The Great Gatsby | "genre"=>"novel", "pages"=>"256", "author"=>"F. Scott Fitzgerald", "freeshipping"=>"yes"
  4 | People           | "date"=>"09.12.2022", "number"=>"38", "publisher"=>"Time Inc", "freeshipping"=>"yes"

Добавление нескольких пар ключ/значение

Чтобы добавить несколько пар ключ/значение, используйте двумерный массив, содержащий ключи и значения.

Следующее выражение заменяет значение поля attributes в строке, где id = 4:

UPDATE products SET attributes = hstore(ARRAY[['pages', '32'],['media', 'ebook']]) WHERE id = 4;

Запрос ниже добавляет новые значения к значению поля attributes в строке, где id = 4:

UPDATE products SET attributes = attributes || hstore(ARRAY[['pages', '32'],['media', 'ebook']]) WHERE id = 4;

Удаление пары ключ/значение

Используйте функцию delete, чтобы удалить пару ключ/значение из столбца hstore:

UPDATE products
SET attributes = delete(attributes, 'freeshipping');

Вы также можете использовать массив для удаления нескольких ключей:

UPDATE products
SET attributes = delete(attributes, ARRAY['genre', 'pages']);

Проверка существования ключа

Используйте оператор ?, чтобы проверить, существует ли определенный ключ в столбце hstore:

SELECT name, attributes -> 'publisher' as publisher
FROM products
WHERE attributes ? 'publisher';

Результат:

  name  | publisher
--------+-----------
 People | Time Inc

Проверка существования пары ключ/значение

Чтобы проверить, существует ли пара ключ/значение, используйте оператор @>, который определяет, содержит ли левый операнд правый. Следующее выражение возвращает все строки, в которых столбец attributes содержит "freeshipping"=>"yes":

SELECT name, attributes -> 'freeshipping' as freeshipping
FROM products
WHERE attributes @> '"freeshipping"=>"yes"' :: hstore;

Результат:

       name       | freeshipping
------------------+--------------
 The Time Machine | yes
 The Great Gatsby | yes
 People           | yes

Получение строк, содержащих указанные ключи

Используйте оператор ?&, чтобы получить строки с несколькими указанными ключами. Следующий запрос возвращает строки с ключами genre и pages:

SELECT name FROM products
WHERE attributes ?& ARRAY [ 'genre', 'pages' ];

Результат:

       name
------------------
 The Time Machine
 The Great Gatsby
 Hyperion

Получение всех ключей

Используйте функцию akeys, чтобы получить все ключи hstore в виде массива:

SELECT akeys (attributes) FROM products;

Результат:

                akeys
--------------------------------------
 {genre,pages,author,freeshipping}
 {genre,pages,author,freeshipping}
 {date,number,publisher,freeshipping}
 {genre,pages,author,freeshipping}

Функция skeys возвращает все ключи в виде набора записей:

SELECT skeys (attributes) FROM products;

Результат:

 genre
 pages
 author
 freeshipping
 genre
 pages
 author
 freeshipping
 date
 number
 publisher
 freeshipping
 genre
 pages
 author
 freeshipping

Получение всех значений

Используйте функцию avals, чтобы получить все значения hstore в виде массива:

SELECT avals (attributes) FROM products;

Результат:

                avals
---------------------------------------
 {sci-fi,202,"Herbert Wells",yes}
 {novel,256,"F. Scott Fitzgerald",yes}
 {09.12.2022,38,"Time Inc",yes}
 {sci-fi,640,"Dan Simmons",no}

Функция svals возвращает все значения в виде набора записей:

SELECT svals (attributes) FROM products;

Результат:

 sci-fi
 202
 Herbert Wells
 yes
 novel
 256
 F. Scott Fitzgerald
 yes
 09.12.2022
 38
 Time Inc
 yes
 sci-fi
 640
 Dan Simmons
 no

Преобразование данных hstore в набор записей

Используйте функцию each для извлечения данных hstore в виде набора записей:

SELECT
    name,
    (each(attributes) ).*
FROM products;

Результат:

       name       |     key      |        value
------------------+--------------+---------------------
 The Time Machine | genre        | sci-fi
 The Time Machine | pages        | 202
 The Time Machine | author       | Herbert Wells
 The Time Machine | freeshipping | yes
 The Great Gatsby | genre        | novel
 The Great Gatsby | pages        | 256
 The Great Gatsby | author       | F. Scott Fitzgerald
 The Great Gatsby | freeshipping | yes
 People           | date         | 09.12.2022
 People           | number       | 38
 People           | publisher    | Time Inc
 People           | freeshipping | yes
 Hyperion         | genre        | sci-fi
 Hyperion         | pages        | 640
 Hyperion         | author       | Dan Simmons
 Hyperion         | freeshipping | no

Преобразование данных hstore в JSON/JSONB

В PostgreSQL есть функции hstore_to_json и hstore_to_jsonb для преобразования данных hstore в JSON или JSONB. Следующий запрос преобразует столбец attributes в JSONB:

SELECT hstore_to_jsonb (attributes) jsonb
FROM products;

Результат:

                                           jsonb
--------------------------------------------------------------------------------------------
 {"genre": "sci-fi", "pages": "202", "author": "Herbert Wells", "freeshipping": "yes"}
 {"genre": "novel", "pages": "256", "author": "F. Scott Fitzgerald", "freeshipping": "yes"}
 {"date": "09.12.2022", "number": "38", "publisher": "Time Inc", "freeshipping": "yes"}
 {"genre": "sci-fi", "pages": "640", "author": "Dan Simmons", "freeshipping": "no"}

Индексирование полей hstore

Вы можете использовать индексы, чтобы сократить время, необходимое PostgreSQL для поиска ключей и значений.

Тип hstore поддерживает GIN и GiST индексацию с операторами @>, ?, ?& и ?|.

Следующие операторы создают индексы GIN и GiST для столбца attributes:

CREATE INDEX idx_gin_attributes ON products USING GIN(attributes);

CREATE INDEX idx_gist_attributes ON products USING GIST(attributes);

Вы также можете использовать индексацию hash и btree с оператором =.

Следующие выражения создают индексы hash и btree для столбца attributes:

CREATE INDEX idx_btree_attributes ON products USING BTREE(attributes);

CREATE INDEX idx_hash_attributes ON products USING HASH(attributes);
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней