Knowledgebase

Introduction

PostgreSQL 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.

Prerequisites

To follow along with this guide, you need:

• A Linux server.
• A non-root user with sudo privileges.
• A PostgreSQL database server.

1. Create a Sample Database

SSH to your Linux server and follow the steps below to create a sample web_store database.

1. Log in to the PostgreSQL database as a postgres user.

    $ sudo -u postgres psql

2. Next, enter your postgres database password and execute the following SQL statement to create a sample web_store database.

    postgres=# CREATE DATABASE web_store;

3. Switch to the new web_store database.

    postgres=# \connect web_store;

4. Install and load the hstore extension into the current database.

    web_store-# CREATE EXTENSION hstore;

5. 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.

2. Insert Data Into the PostgreSQL hstore Column

The 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.

1. 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

2. 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

3. 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.

3. Manipulate Data in a PostgreSQL hstore Column

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.

1. 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;

2. 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)

3. 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;

4. 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)

5. 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)

6. 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)

7. 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

8. 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)

9. 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

10. 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

11. 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)

12. You've now tested most of the PostgreSQL hstore functions, and they've worked as expected.

Conclusion

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.