HOWTO handle key-value data in PostgreSQL – the HSTORE contrib

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.

Leave A Comment