Author: Francis Ndungu
Last Updated: Thu, Jan 20, 2022PostgreSQL is one of the most advanced relational database management systems (RDBMS). It has extensive security features such as role-based permissions, transport-level security (SSL/TLS), and network-level security using TCP/IP sockets.
In addition, the community-driven RDBMS is reliable, scalable, and supports modern data types, including hstore
, array
, and user-defined
types. The hstore
data type has a wide array of applications, and you can use it to store semi-structured data as key-value pairsâfor example, customers addresses, books, meta information, and more.
The hstore
data type provides you with the flexibility of storing, retrieving, and manipulating data without the need to conform to a rigid database schema. In other words, you don't have to use a strict structural design when designing your database tables.
In this guide, you'll implement the PostgreSQL hstore
data type to store customers' address information on your Linux server.
To follow along with this guide, you need:
SSH to your Linux server and follow the steps below to create a sample web_store
database.
Log in to the PostgreSQL database as a postgres
user.
$ sudo -u postgres psql
Next, enter your postgres
database password and execute the following SQL statement to create a sample web_store
database.
postgres=# CREATE DATABASE web_store;
Switch to the new web_store
database.
postgres=# \connect web_store;
Install and load the hstore
extension into the current database.
web_store-# CREATE EXTENSION hstore;
Next, create a customers
table with the following schema. While you'll use the customer_id
, first_name
, last_name
, and phone
columns to store structured data, you'll use the hstore
data type in the address
column to store key-value pairs of your customers' address information.
web_store-# CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR (50),
last_name VARCHAR (50),
phone VARCHAR (15),
address hstore
);
Your database schema is now ready. You'll insert sample data in the customers
table in the next step.
hstore
ColumnThe address column in the customers
table can accept varying customers' address information without using a rigid schema.
To put this in a better perspective, you might require customers to provide their addresses in the following format in a real-life application scenario.
address_line_1
address_line_2
city
state
zip
However, some customers might exclude one or several of these attributes (for instance, some might omit the field address_line_2
) when providing their address information. Also, the naming convention of different customers' addresses may differ. For example, some might use the word town to refer to city, and others may use zip to mean a post code.
To address these types of inconsistencies, this is where the PostgreSQL hstore
data type comes into play. You should consider using the hstore
data type on any table column that exhibits an unpredictable model that can be organized in key-value pairs. You'll understand this better after working with a few samples below.
Insert a sample record into the customers
table. Enter the address format using a comma-separated key =>value
pairs. Remember to double-quote""
both the keys
and the values
.
web_store# INSERT INTO customers (
first_name,
last_name,
phone,
address
)
VALUES (
'JOHN',
'DOE',
'11111',
'
"address_line_1" => "SAMPLE STREET",
"address_line_2" => "APT. 34",
"city" => "ATHENS",
"state" => "ALABAMA",
"zip" => "123"
'
);
Output.
web_store# INSERT 0 1
Next, insert another record. This time around, swap the city
and zip
key names with town
and post code
, respectively.
web_store# INSERT INTO customers (
first_name,
last_name,
phone,
address
)
VALUES (
'MARY',
'SMITH',
'22222',
'
"address_line_1" => "NTH STREET",
"address_line_2" => "SPIRAL HOUSE",
"town" => "CHICAGO",
"state" => "ILLINOIS",
"post code" => "456"
'
);
Output.
web_store# INSERT 0 1
Still on the customers
table, insert another record and exclude the address_line_2
key. This is where the beauty of the hstore
data type starts. When inserting the records, you don't need to stick to a preset database schema.
web_store# INSERT INTO customers (
first_name,
last_name,
phone,
address
)
VALUES (
'JANE',
'MARK',
'33333',
'
"address_line_1" => "5TH STREET",
"city" => "COLUMBUS",
"state" => "OHIO",
"zip" => "777"
'
);
You now have some sample records. In the next step, you'll use some SQL data manipulation language(DML) statements on the customers
table to query, update, and delete the key-value pairs.
Like other data columns, the PostgreSQL hstore
column accepts a wide array of DML commands. Apart from the INSERT
statement, you can also UPDATE
, DELETE
, and SELECT
records from the hstore
fields.
To retrieve all the records from the customers
table, execute the following SELECT
statement.
web_store# SELECT
customer_id,
first_name,
last_name,
phone,
address
FROM customers;
You should get the following output.
customer_id | first_name | last_name | phone | address
-------------+------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------
1 | JOHN | DOE | 11111 | "zip"=>"123", "city"=>"ATHENS", "state"=>"ALABAMA", "address_line_1"=>"SAMPLE STREET", "address_line_2"=>"APT. 34"
2 | MARY | SMITH | 22222 | "town"=>"CHICAGO", "state"=>"ILLINOIS", "post code"=>"456", "address_line_1"=>"NTH STREET", "address_line_2"=>"SPIRAL HOUSE"
3 | JANE | MARK | 33333 | "zip"=>"777", "city"=>"COLUMBUS", "state"=>"OHIO", "address_line_1"=>"5TH STREET"
(3 rows)
Press Q to exit from the PostgreSQL query viewer. Next, use the SELECT SAMPLE_HSTORE_COLUMN -> 'SAMPLE_KEY_NAME' FROM SAMPLE_TABLE_NAME
syntax to SELECT
specific keys from the hstore
address column, . For instance, execute the following SQL command to return the customers' states alongside their names. Remember to surround the key you're retrieving with single quotes ''
.
web_store# SELECT
customer_id,
first_name,
last_name,
phone,
address -> 'state' as state
FROM customers;
You should get the following result.
customer_id | first_name | last_name | phone | state
-------------+------------+-----------+-------+----------
1 | JOHN | DOE | 11111 | ALABAMA
2 | MARY | SMITH | 22222 | ILLINOIS
3 | JANE | MARK | 33333 | OHIO
(3 rows)
When entering the sample data in the address
column, you've used the word city
and town
interchangeably. To return the customers cities
and towns
respectively, run the following statements.
City:
web_store# SELECT
customer_id,
first_name,
last_name,
phone,
address -> 'city' as REGION
FROM customers;
Output.
customer_id | first_name | last_name | phone | region
-------------+------------+-----------+-------+----------
1 | JOHN | DOE | 11111 | ATHENS
2 | MARY | SMITH | 22222 |
3 | JANE | MARK | 33333 | COLUMBUS
(3 rows)
Town:
web_store# SELECT
customer_id,
first_name,
last_name,
phone,
address -> 'town' as REGION
FROM customers;
Output.
customer_id | first_name | last_name | phone | region
-------------+------------+-----------+-------+---------
1 | JOHN | DOE | 11111 |
2 | MARY | SMITH | 22222 | CHICAGO
3 | JANE | MARK | 33333 |
(3 rows)
To use the value of a certain key in the WHERE
clause, use the syntax ... WHERE SAMPLE_HSTORE_COLUMN -> 'SAMPLE_KEY_NAME' SAMPLE_CONDITION
. For instance, run the command below to retrieve all the customers from the COLUMBUS
city.
web_store# SELECT
customer_id,
first_name,
last_name,
phone,
address -> 'city' as city
FROM customers
WHERE address -> 'city' = 'COLUMBUS'
;
Output.
customer_id | first_name | last_name | phone | city
-------------+------------+-----------+-------+----------
3 | JANE | MARK | 33333 | COLUMBUS
(1 row)
Next, use the syntax UPDATE SAMPLE_TABLE SET SAMPLE_HSTORE_COLUMN = SAMPLE_HSTORE_COLUMN || '"SAMPLE_KEY_NAME"=>"SAMPLE_KEY_VALUE"' :: hstore;
to add a new key-value pair to an existing table. For instance, to add the country
key in the address
column, run the following command.
web_store# UPDATE customers SET
address = address || '"country"=>"US"' :: hstore;
Output.
UPDATE 3
Confirm the changes by running the following SELECT
statement against the customers
table.
web_store# SELECT
customer_id,
first_name,
last_name,
phone,
address -> 'country' as country
FROM customers;
Output.
customer_id | first_name | last_name | phone | country
-------------+------------+-----------+-------+---------
1 | JOHN | DOE | 11111 | US
2 | MARY | SMITH | 22222 | US
3 | JANE | MARK | 33333 | US
(3 rows)
To update a key, use the syntax UPDATE SAMPLE_TABLE SET SAMPLE_HSTORE_COLUMN = SAMPLE_HSTORE_COLUMN || '"SAMPLE_KEY_NAME"=>"SAMPLE_KEY_NAME"' :: hstore;
. For example, to update the new country
key value to USA
, execute the statement below.
web_store# UPDATE customers
SET address = address || '"country"=>"USA"' :: hstore;
Output.
UPDATE 3
To remove a key from a table, use the syntax UPDATE SAMPLE_TABLE SET SAMPLE_HSTORE_COLUMN = DELETE(SAMPLE_HSTORE_COLUMN, 'SAMPLE_KEY_NAME');
. For instance, execute the statement below to completely remove the country
key from the customers
table.
web_store# UPDATE customers
SET address = DELETE(address, 'country');
Output.
UPDATE 3
Use the PostgreSQL inbuilt hstore_to_json()
function to convert output from a hstore
column to JSON.
web_store# SELECT
customer_id,
first_name,
last_name,
phone,
hstore_to_json(address)
FROM customers;
Output.
customer_id | first_name | last_name | phone | hstore_to_json
-------------+------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------
1 | JOHN | DOE | 11111 | {"zip": "123", "city": "ATHENS", "state": "ALABAMA", "address_line_1": "SAMPLE STREET", "address_line_2": "APT. 34"}
2 | MARY | SMITH | 22222 | {"town": "CHICAGO", "state": "ILLINOIS", "post code": "456", "address_line_1": "NTH STREET", "address_line_2": "SPIRAL HOUSE"}
3 | JANE | MARK | 33333 | {"zip": "777", "city": "COLUMBUS", "state": "OHIO", "address_line_1": "5TH STREET"}
(3 rows)
You've now tested most of the PostgreSQL hstore
functions, and they've worked as expected.
In this guide, you've created a sample database and a table. You've then implemented the versatile hstore
data type to store semi-structured data for your customers' addresses in the PostgreSQL database.
Although this is not a conclusive list of all the PostgreSQL hstore
functions, it should get you started with storing semi-structured data on a database. Also, the hstore
columns work well with ancillary data, therefore, it is advisable to use it with data you don't intend to look up with indices frequently.