Postgres key-value Reference Card
I have been spending more time in postgres using its hstore/json fields. It’s a great way to augment a relational database with a slick key-value store. I like this combination of relational and key-value a lot.
One thing I have found confusing, especially working the key-value fields from Rails: the query format for different key-value fields and how to do common key-value operations such as: records with certain keys, records with a key value, etc.
There are many similarities with each type of field, but enough differences where I have to drop right into a SQL console to write things out myself.
Instead, here is a reference card for how to query for records with a:
- key that exist in the field
- key not in the field
- key equaling a value
- key not equaling a value
- key with a group of values
- specific value (regardless of key)
- not a specific value (regardless of key)
- list of all keys in a key-value column
and a tip I found I always need when working with the key-value field in Ruby on Rails.
Let’s get started with a fresh postgres install and setup a table to try stuff out.
Install and setup postgres
This is how I installed and setup postgres for the examples
Setup:
- Add latest postgres repo
- Install postgres > 9.4 & postgres-contrib (for hstore)
note: postgres 9.4 introduced JSONB. Also, Ubuntu 14.04’s default for postgres is still 9.3!
- login as postgres superuser and change its password:
- add user
note: I grant user superuser privilege because hstore requires a user to create extension an extension on a table.
Create Database & Table
- log into the
sample
database on postgres:
- enable
hstore
extension
- create table
stuff
Sample Data
The following records are the ones to be used with the commands below.
Current table entries
Result:
id | data_hstore | data_json | data_jsonb
----+-------------+--------------+------------
1 | "a"=>"b" | { "a": "b" } | {"a": "b"}
2 | "a"=>"c" | { "a": "c" } | {"a": "c"}
3 | "a"=>"d" | { "a": "d" } | {"a": "d"}
4 | "e"=>"b" | { "e": "b" } | {"e": "b"}
(4 rows)
Accessors
As far as I can tell from the postgres documentation:
->
returns text->>
returns an object
So, from my experience, ->
will work with hstore, json, and jsonb
types. ->>
only works with json and jsonb types.
Keep an eye out on the accessor being used in the examples below.
Records with a key that has any value
Basically, any records that has a particular key. Great for filtering out records where a key is known, but not all of its values.
id | data_hstore | data_json | data_jsonb
----+-------------+--------------+------------
1 | "a"=>"b" | { "a": "b" } | {"a": "b"}
2 | "a"=>"c" | { "a": "c" } | {"a": "c"}
3 | "a"=>"d" | { "a": "d" } | {"a": "d"}
(3 rows)
Records without a key
The inverse of the previous query, filter all records without a key.
Result:
id | data_hstore | data_json | data_jsonb
----+-------------+--------------+------------
4 | "e"=>"b" | { "e": "b" } | {"e": "b"}
(1 row)
Records with a key equal to a value
Any records with a specific key-value pair.
Result:
id | data_hstore | data_json | data_jsonb
----+-------------+--------------+------------
1 | "a"=>"b" | { "a": "b" } | {"a": "b"}
(1 row)
note: accessors for hstore and json/jsonb differ here.
Records where a key does not have a value
The inverse of the previous query, filter out all key-value pairs.
Result:
id | data_hstore | data_json | data_jsonb
----+-------------+--------------+------------
2 | "a"=>"c" | { "a": "c" } | {"a": "c"}
3 | "a"=>"d" | { "a": "d" } | {"a": "d"}
x(2 rows)
Records with a key equal to a group of values
This is a great one to filter for a key to a known set of values.
Result:
id | data_hstore | data_json | data_jsonb
----+-------------+--------------+------------
1 | "a"=>"b" | { "a": "b" } | {"a": "b"}
2 | "a"=>"c" | { "a": "c" } | {"a": "c"}
(2 rows)
This answer was very insightful in the JSONB query. I am a bit surprised there isn’t something similar for the JSON query.
Records which any key has a value:
Result:
id | data_hstore | data_json | data_jsonb
----+-------------+--------------+------------
1 | "a"=>"b" | { "a": "b" } | {"a": "b"}
4 | "e"=>"b" | { "e": "b" } | {"e": "b"}
(2 rows)
This will only work reliably when a value never exists as a key.
Records which any key does not have a value:
Result:
id | data_hstore | data_json | data_jsonb
----+-------------+--------------+------------
2 | "a"=>"c" | { "a": "c" } | {"a": "c"}
3 | "a"=>"d" | { "a": "d" } | {"a": "d"}
(2 rows)
Thanks to Sergey for for showing the way to search for any key with a value.
Get all possible keys in a column
Return all the keys that exists in a key/value column of a table.
Result:
fields
--------
a
a
a
e
(4 rows)
If the keys are unknown ahead of time, this is essential to finding out all the possible keys that maybe in a key-value field!
HSTORE fields use skeys
while JSON and JSONB use json_object_keys
.
For Rails / ActiveRecord
For some reason, the ‘sql’ part of the query must contain parenthesis for the key-value item, like so:
Otherwise, there will be a database transaction error on the connection and it will have to be reset.
Conclusion
A quick reference on how to select items from the Postgresql’s key value field: hstore, json, and jsonb.
If there are any improvements to the examples or additional ones you want to see, tweet me (@redgreenrepeat) or send me a quick email (andrew_at_redgreenrepeat.com)