The NoSQL data type
In this section, we will approach the NoSQL data types that are present in PostgreSQL. Since this book is not specifically focused on NoSQL, we will just take a quick look.
PostgreSQL handles the following NoSQL data types:
hstorexmljson/jsonb
We will now talk about hstore and json.
The hstore data type
hstore was the first NoSQL data type that was implemented in PostgreSQL. This data type is used for storing key-value pairs in a single value. Before working with the hstore data type, we need to enable the hstore extension on our server:
forumdb=> create extension hstore ;
CREATE EXTENSION
Let’s look at how we can use the hstore data type with an example. Suppose that we want to show all posts with their usernames and their categories:
forumdb=> select p.pk,p.title,u.username,c.title as category
from posts p
inner join users u on p.author=u.pk
left join categories c on p.category=c.pk
order...