PostgreSQL: Полезные команды и примеры их работы с расширением hstore

hstore - модуль для БД PostgreSQL, добавляющий новый тип данных "hstore" для хранения пар ключ-значение с обширным набором функций и операторов, ключи и значения могут быть только строками. Довольно удобно использовать hstore под хранение флагов, когда их может быть достаточно большое количество.

Ниже представлены примеры кода при использовании расширения, которые могут быть полезны в работе:

1) Выбор значения флага, возвращает 0, если ключ не найден:

SELECT 
        CAST(COALESCE(table.hstore_field->'key', '0') AS integer) as column_name 
    FROM table;

2) Обновление значения существующего поля:

UPDATE table
    SET hstore_column = hstore_column || hstore('hstore_key', 'ваше значение')
    WHERE id = 111;

3) Получаем все пары ключ\значение от строки:

SELECT key AS name, value FROM EACH(
    (SELECT options
        FROM site
        WHERE site_id=?)
);

4) Пример запроса с указанием hstore в возвращаемых значениях и условии:

SELECT 
        tags->'name' AS name, 
        tags->'place' AS place 
    FROM ways 
    WHERE 
        tags->'place' IN ('city','town', 'village');

5) Примеры добавления записи в таблицу:

INSERT INTO hstore_test (data) values ('key1 => 123, key2 => "text"'::hstore);

INSERT INTO books (name, attributes) VALUES (
    'Harry Potter',
    'author    => "Joanne Rowling",
    pages     => 500,
    category  => "fantasy"'
);

6) Добавление индекса для hstore колонки:

CREATE INDEX ON test USING GIN(value);

или вот так:

CREATE INDEX ON test USING GIST(value);

Postgres позволяет создавать индексы на ключах Hstore, теперь при поиске элемента по ключу это будет не полное сканирование таблицы, а поиск по индексу. По этой ссылке можно подробней почитать разницу между индексами GIN и GIST.

7) Поиск записей, у которых существует hstore поле с определенным ключом:

SELECT title, text FROM items WHERE exist(hstore_column_name, 'имя_ключа');

8) Удаляем пару ключ\значение из hstore колонки у записи:

UPDATE books
    SET extra_params = delete(extra_params,'book_read')
    WHERE id = 1