Examples to hstore in PostgreSQL

 

Testing environment

test=# select version();
                                                  version
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
(1 row)

Install extension(s)

test=# create extension hstore;
CREATE EXTENSION

test=# \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description
---------+---------+------------+--------------------------------------------------
 hstore  | 1.8     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Create table

test=# create table hstore_data (data hstore);
CREATE TABLE

test=# \dt+ hstore_data
                                        List of relations
 Schema |    Name     | Type  |  Owner   | Persistence | Access method |    Size    | Description
--------+-------------+-------+----------+-------------+---------------+------------+-------------
 public | hstore_data | table | postgres | permanent   | heap          | 8192 bytes |
(1 row)

Insert record(s)

test=# insert into hstore_data values('"key1"=>"val1",key2=>val2,"key3 with space"=>val3,key4=>NULL');
INSERT 0 1

Query record(s)

select data['key1'] as key1,
       data['key2'] as key2,
       data['key3 with space'] as key3,
       data['key4'] as key4
from hstore_data;

test=# select data['key1'] as key1,
test-#        data['key2'] as key2,
test-#        data['key3 with space'] as key3,
test-#        data['key4'] as key4
test-# from hstore_data;
 key1 | key2 | key3 | key4
------+------+------+------
 val1 | val2 | val3 |
(1 row)

Update record(s)

test=# update hstore_data set data['key1']='val1a';
UPDATE 1

Query record(s)

test=# select data['key1'] as key1,
       data['key2'] as key2,
       data['key3 with space'] as key3,
       data['key4'] as key4
from hstore_data;
 key1  | key2 | key3 | key4
-------+------+------+------
 val1a | val2 | val3 |
(1 row)

Reference: https://www.postgresql.org/docs/current/hstore.html