Intro to HStore
In this episode, I'm going to demonstrate how to use the HStore datatype in Postgres. This episode is brought to you by Hashrocket, a consultancy with expertise in PostgreSQL - visit us at https://hashrocket.com and www.pgcasts.com
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.
Watch video Intro to HStore online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 22 June 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 4,244 once and liked it 51 people.