The Postgres extension hstore implements a new data type (HSTORE) for storing key-value pairs in a single value.
The hstore data type is super handy for storing semi-structured data or rows with many attributes that are rarely queried.
First extension must be enabled.
yugabyte=# CREATE EXTENSION hstore;
CREATE EXTENSION
Now I can create a table where a column can have a data type of HSTORE.
yugabyte=# CREATE TABLE emp (id INT PRIMARY KEY, name VARCHAR, attributes HSTORE);
CREATE TABLE
When insterting data into an HSTORE column, data is listed as key-value pairs using the => operator.
yugabyte=# INSERT INTO emp (id, name, attributes)
yugabyte-# VALUES (1,
yugabyte(# 'Jim',
yugabyte(# ('"age" => "43",
yugabyte'# "salary" => "100000",
yugabyte'# "hire_date" => "03/19/2021"')
yugabyte(# );
INSERT 0 1
yugabyte=# SELECT * FROM emp;
id | name | attributes
----+------+------------------------------------------------------------
1 | Jim | "age"=>"43", "salary"=>"100000", "hire_date"=>"03/19/2021"
(1 row)
The hstore extension provides the -> operator to query the value of a specific key from an HSTORE column.
yugabyte=# SELECT attributes -> 'hire_date' AS hire_date FROM emp WHERE name = 'Jim';
hire_date
------------
03/19/2021
(1 row)
Have Fun!