I previously blogged about NoSQL support in PostgreSQL and then later wrote a tutorial on using JSON data type. Today, I will be talking about another angle of NoSQL in PostgreSQL, handling key-value data using the HSTORE contrib module.
HSTORE was introduced back in 2006 as part of PostgreSQL 8.2, and I doubt the authors had any idea at the time how NoSQL popularity will skyrocket in the coming years. Using HSTORE, developers can emulate a schema-less semi-structured data store while staying within the fully ACID-compliant domain of PostgreSQL, a relational database.
Without further ado, here is how some basic HSTORE functionality can be handled …
Create the extension
The very first step is to create the HSTORE extension, essentially loading the contrib module to your PostgreSQL instance.
hstore_db=# CREATE EXTENSION hstore;
CREATE EXTENSION
Create a table with HSTORE data type
Once the extension is loaded, creating a table with a column of HSTORE data type is fairly straightforward. The syntax is exactly the same as any native data type.
hstore_db=# CREATE TABLE hstore_test ( id SERIAL, sale HSTORE );
CREATE TABLE
Insert data into HSTORE column
You can use the ‘=>’operator to assign values to keys while you insert data.
hstore_db=# INSERT INTO hstore_test (sale) VALUES (‘”milk”=>”4″, “bread”=>”2”, “bananas”=>”12”, “cereal”=>”1″‘);
INSERT 0 1
Retrieve data from an HSTORE column
HSTORE data can be retrieved like any other native PostgreSQL data type.
hstore_db=# SELECT sale FROM hstore_test;
sale
———————————————————–
“milk”=>”4”, “bread”=>”2”, “cereal”=>”1”, “bananas”=>”12”
(1 row)
Retrieve value for a particular key
HSTORE provides the ‘->’ operator to retrieve value of a particular key from the table.
hstore_db=# SELECT sale -> ‘bread’ AS quantity FROM hstore_test;
quantity
———-
2
(1 row)
Use value of a key in the WHERE clause
Similarly, the ‘->’ operator can be used to specify a values in the extract criteria of a query, i.e. as part of the WHERE clause.
hstore_db=# SELECT * FROM hstore_test WHERE sale -> ‘bananas’ = ’12’;
id | sale
—-+———————————————————–
1 | “milk”=>”4”, “bread”=>”2”, “cereal”=>”1”, “bananas”=>”12”
(1 row)
Remove a key-value pair from a row
PostgreSQL provides for deleting a specific key-value from an existing HSTORE row.
hstore_db=# UPDATE hstore_test SET sale = delete(sale, ‘bread’);
UPDATE 1
hstore_db=# SELECT sale FROM hstore_test;
sale
———————————————
“milk”=>”4”, “cereal”=>”1”, “bananas”=>”12”
(1 row)
Add a key-value pair to an existing row
PostgreSQL also gives you the ability to add a key-value pair to an existing HSTORE row.
hstore_db=# UPDATE hstore_test SET sale = sale || ‘”cupcakes”=>”20″‘::hstore;
UPDATE 1
hstore_db=# SELECT sale FROM hstore_test;
sale
—————————————————————
“milk”=>”4”, “cereal”=>”1”, “bananas”=>”12”, “cupcakes”=>”20”
(1 row)
Update value of a key
Like add and delete, you can also update the value of a particular key within an existing HSTORE row.
hstore_db=# UPDATE hstore_test SET sale = sale || ‘”cereal”=>”5″‘::hstore;
UPDATE 1
hstore_db=# SELECT sale FROM hstore_test;
sale
—————————————————————
“milk”=>”4”, “cereal”=>”5”, “bananas”=>”12”, “cupcakes”=>”20”
(1 row)
Check for a specific key
In order to illustrate how to check for specific keys in an HSTORE column, first let’s insert a 2nd row in the hstore_test table.
hstore_db=# INSERT INTO hstore_test (sale) VALUES (‘”apples”=>”10″, “oranges”=>”12”, “mangoes”=>”6″‘);
INSERT 0 1
hstore_db=# SELECT * FROM hstore_test;
id | sale
—-+—————————————————————
1 | “milk”=>”4”, “cereal”=>”5”, “bananas”=>”12”, “cupcakes”=>”20”
2 | “apples”=>”10”, “mangoes”=>”6”, “oranges”=>”12”
(2 rows)
Here is how you can retrieve the record that contains the key ‘bananas’.
hstore_db=# SELECT * FROM hstore_test WHERE sale ? ‘bananas’;
id | sale
—-+—————————————————————
1 | “milk”=>”4”, “cereal”=>”5”, “bananas”=>”12”, “cupcakes”=>”20”
(1 row)
The query below retrieves the newly inserted record, as the key specified is ‘oranges’.
hstore_db=# SELECT * FROM hstore_test WHERE sale ? ‘oranges’;
id | sale
—-+————————————————-
2 | “apples”=>”10”, “mangoes”=>”6”, “oranges”=>”12”
(1 row)
Check for a specific key-value pair
You can retrieve HSTORE data based on a specific key-value pair using the ‘@>’ operator.
hstore_db=# SELECT * FROM hstore_test WHERE sale @> ‘”mangoes”=>”6″‘::hstore;
id | sale
—-+————————————————-
2 | “apples”=>”10”, “mangoes”=>”6”, “oranges”=>”12”
(1 row)
Retrieve records that contain all specified keys
You can specify a logical ‘AND’ using the ‘?&’ operator in the extract criteria.
hstore_db=# SELECT * FROM hstore_test WHERE sale ?& ARRAY[‘milk’,’apples’];
id | sale
—-+——
(0 rows)
hstore_db=# SELECT * FROM hstore_test WHERE sale ?& ARRAY[‘oranges’,’apples’];
id | sale
—-+————————————————-
2 | “apples”=>”10”, “mangoes”=>”6”, “oranges”=>”12”
(1 row)
Retrieve records that contain any of the specified keys
Conversely, you can also specify a logical ‘OR’ using the ‘?|’ operator in the extract criteria.
hstore_db=# SELECT * FROM hstore_test WHERE sale ?| ARRAY[‘milk’,’apples’];
id | sale
—-+—————————————————————
1 | “milk”=>”4”, “cereal”=>”5”, “bananas”=>”12”, “cupcakes”=>”20”
2 | “apples”=>”10”, “mangoes”=>”6”, “oranges”=>”12”
(2 rows)
Retrieve all keys from the HSTORE column
You can retrieve all available keys from the HSTORE data as an array using the ‘akeys’ function.
hstore_db=# SELECT akeys(sale) FROM hstore_test;
akeys
——————————–
{milk,cereal,bananas,cupcakes}
{apples,mangoes,oranges}
(2 rows)
… or as a set using the ‘skeys’ function.
hstore_db=# SELECT skeys(sale) FROM hstore_test;
skeys
———-
milk
cereal
bananas
cupcakes
apples
mangoes
oranges
(7 rows)
Retrieve all values from the HSTORE column
Similarly, you can retrieve all available values from the HSTORE data as an array using the ‘avals’ function.
hstore_db=# SELECT avals(sale) FROM hstore_test;
avals
————-
{4,5,12,20}
{10,6,12}
(2 rows)
… or as a set using the ‘svals’ function.
hstore_db=# SELECT svals(sale) FROM hstore_test;
svals
——-
4
5
12
20
10
6
12
(7 rows)
Convert key-value pairs to JSON
The interoperability between JSON and HSTORE is simply a joy to work with. Simply use the hstore_to_json function and you have converted your key-value database into a document database.
hstore_db=# SELECT hstore_to_json(sale) FROM hstore_test;
hstore_to_json
—————————————————————–
{“milk”: “4”, “cereal”: “5”, “bananas”: “12”, “cupcakes”: “20”}
{“apples”: “10”, “mangoes”: “6”, “oranges”: “12”}
(2 rows)
Convert key-value pairs to sets
For relational purposes, the key-value pairs can also be retrieved as a set.
hstore_db=# SELECT id, (each(sale)).* FROM hstore_test;
id | key | value
—-+———-+——-
1 | milk | 4
1 | cereal | 5
1 | bananas | 12
1 | cupcakes | 20
2 | apples | 10
2 | mangoes | 6
2 | oranges | 12
(7 rows)
PostgreSQL official documentation has a more comprehensive listing of the available HSTORE functions and operators.