PostgreSQL NoSQL Tutorial: Getting Started with JSONB and Hstore

PostgreSQL is a powerful and popular open-source database. It is known for its strong relational features. But did you know it also supports NoSQL features? Yes, PostgreSQL can store unstructured data like a NoSQL database. It does this using two special types: JSONB and Hstore. In this tutorial, you will learn how to use both. We will explain each in simple steps. This guide is perfect for beginners and developers trying PostgreSQL NoSQL.

What Is PostgreSQL NoSQL?

PostgreSQL is mainly a relational database. But it also allows NoSQL-style data storage. You can store key-value pairs and JSON documents. This means you can work with flexible data formats. That’s why people call it a PostgreSQL NoSQL hybrid. You get the best of both worlds—SQL and NoSQL.

Why Use NoSQL in PostgreSQL?

Here are some common reasons:

• Store flexible or unknown data structures
• Handle user settings or logs
• Build APIs with dynamic content
• Avoid extra tables for simple data

With NoSQL in PostgreSQL, you don’t need to change your schema often.

Key PostgreSQL NoSQL Types

PostgreSQL offers two powerful NoSQL data types:

1. JSON/JSONB

These store JSON (JavaScript Object Notation) data. JSONB is a binary version, which is faster and better indexed.

2. Hstore

This stores simple key-value pairs like a dictionary. It’s ideal for flat, one-level data without nesting. Let’s learn how to use both in easy steps.

Part 1: Working with JSONB in PostgreSQL

Step 1: Create a Table with a JSONB Column

sqlCopyEditCREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  data JSONB
);

In this example, data can hold a JSON object.

Step 2: Insert JSONB Data

sqlCopyEditINSERT INTO users (name, data)  
VALUES (
  'John Doe',
  '{"age": 30, "city": "New York"}'
);

Here, the data column stores age and city inside a JSON object.

Step 3: Query JSONB Data

Get users who live in New York:

sqlCopyEditSELECT * FROM users  
WHERE data->>'city' = 'New York';

The ->> operator extracts text from a JSON field.

Step 4: Update JSONB Data

Add a new key to the existing JSONB:

sqlCopyEditUPDATE users  
SET data = jsonb_set(data, '{email}', '"john@example.com"')  
WHERE name = 'John Doe';

This adds an email field to the data JSONB.

Step 5: Index JSONB Data

Indexing improves query speed:

sqlCopyEditCREATE INDEX idx_data_city  
ON users ((data->>'city'));

Now queries using the city field will run faster.

Part 2: Using Hstore in PostgreSQL

Step 1: Enable Hstore Extension

sqlCopyEditCREATE EXTENSION IF NOT EXISTS hstore;

You must enable Hstore before using it.

Step 2: Create a Table with Hstore

sqlCopyEditCREATE TABLE settings (
  id SERIAL PRIMARY KEY,
  user_id INT,
  preferences HSTORE
);

Here, preferences stores simple key-value pairs.

Step 3: Insert Hstore Data

sqlCopyEditINSERT INTO settings (user_id, preferences)  
VALUES (
  1,
  'theme => dark, notifications => on'
);

The Hstore field stores two preferences.

Step 4: Query Hstore Data

Get rows where notifications are on:

sqlCopyEditSELECT * FROM settings  
WHERE preferences -> 'notifications' = 'on';

Use -> to access Hstore values.

Step 5: Update Hstore Data

Add or update a key-value pair:

sqlCopyEditUPDATE settings  
SET preferences = preferences || 'language => en'  
WHERE user_id = 1;

This adds a new language preference.

Step 6: Delete a Key from Hstore

sqlCopyEditUPDATE settings  
SET preferences = delete(preferences, 'theme')  
WHERE user_id = 1;

This removes the theme key from the Hstore field.

When to Use JSONB vs Hstore

Feature	JSONB	Hstore
Data type	Nested structures	Flat key-value pairs
Performance	Slower for small data	Fast for small data
Indexing	Supports GIN/BTREE	Supports GIN/BTREE
Use cases	APIs, documents, logs	Settings, metadata, tags

Use JSONB for complex and nested data. Use Hstore for small, flat key-value data.

Real-Life Example: User Profile with JSONB

Suppose you are building a social media app. Each user has different fields, such as bio, likes, and settings.

Use a JSONB column for storing dynamic fields:

sqlCopyEdit{
  "bio": "Music lover",
  "likes": ["rock", "jazz"],
  "dark_mode": true
}

You can add or remove fields without changing the schema.

Real-Life Example: App Settings with Hstore

In a blogging app, users can change preferences. Use Hstore to store options like font size or layout:

sqlCopyEdit'font => large, layout => grid'

You can store and update settings easily.

Pros of Using PostgreSQL NoSQL

• No need to switch to MongoDB or other NoSQL databases
• Use NoSQL and SQL in the same database
• Great for hybrid data models
• Built-in indexing for speed
• Easy to scale with structured and unstructured data

Final Thoughts

PostgreSQL NoSQL features offer great flexibility. With JSONB and Hstore, you can handle dynamic and key-value data easily. You don’t need extra databases or tools. Use JSONB for complex and nested data. Use Hstore for light, simple key-value needs. PostgreSQL is more than just a relational database. It is a complete solution for modern applications. Try JSONB and Hstore today and explore powerful new ways to work with data.