Transcript

Imagine we have an application with users on different devices, logging in from multiple locations. To better understand our users, we want to collect the time and IP address of every new login session.

We need to store this data, without polluting the users table with many new columns. We don't care if the data is relational, and we don't plan to examine it often.

Postgres provides a non-relational datatype for this situation, called hstore. Hstore is a set of zero or more key-value pairs, separated by commas. Keys and values are text strings.

In this episode, we will create an hstore column, populate it with some data, discuss querying that data, and remove the data.

Let's check out our users table: 

\d users;

We have one user:

table users;

Let's create an hstore column to record our session data.

First, we load the hstore extension:

create extension hstore;

Next, we add the hstore column:

alter table users add column session_data hstore not null 
  default ''::hstore;

Let's check the table:

\d users;

There's our hstore.

Next, let's insert into the hstore.

Our user Jack is logging in. Let's capture his IP and timestamp.

Each key in an hstore must be unique, so for this feature, timestamp makes an ideal key:

update users
  set session_data = session_data || hstore(extract(epoch 
  from now())::text, '64.107.86.0')
  where id = 1;

Let's check out that update:

table users;

Jack logs in a few seconds later, and we capture it:

update users
  set session_data = session_data || hstore(extract(epoch 
  from now())::text, '74.107.86.0')
  where id = 1;

With this data type, you can set it up and forget about it. Later, come back and query the data with psql or your ORM of choice. For this problem, we could filter the results by time, or search for a specific IP address.

Removing a key-value pair is easy. We just pass the key as an argument to delete.

update users set session_data = delete(session_data, '1461263782.21855');

These are some of the basic features of Postgres hstore.

That's it for this episode. Thanks for watching.