Extensions (pg_trgm, uuid-ossp, hstore) in PostgreSQL - Mini Project: Build & Apply

Using PostgreSQL Extensions: pg_trgm, uuid-ossp, and hstore

📖 Scenario: You are setting up a PostgreSQL database for a small online bookstore. To improve search capabilities, generate unique identifiers for books, and store flexible metadata about books, you will use PostgreSQL extensions.

🎯 Goal: Learn how to enable and use the PostgreSQL extensions pg_trgm, uuid-ossp, and hstore by creating a table for books with a UUID primary key, a title searchable with trigram similarity, and a metadata column using hstore.

📋 What You'll Learn

Enable the pg_trgm extension

Enable the uuid-ossp extension

Enable the hstore extension

Create a table books with columns id (UUID primary key), title (text), and metadata (hstore)

Insert a sample book with a generated UUID, a title, and some metadata

Write a query to find books with titles similar to a given search term using pg_trgm

💡 Why This Matters

🌍 Real World

Many applications need flexible metadata storage, unique identifiers, and efficient text search. PostgreSQL extensions like pg_trgm, uuid-ossp, and hstore help achieve these features easily.

💼 Career

Database developers and administrators often enable and use extensions to enhance database capabilities for real-world applications such as search engines, content management, and data integration.

1

Enable the PostgreSQL extensions

Write SQL commands to enable the extensions pg_trgm, uuid-ossp, and hstore in the current database.

PostgreSQL

-- Enable the pg_trgm extension
-- Enable the uuid-ossp extension
-- Enable the hstore extension

Need a hint?

Use CREATE EXTENSION IF NOT EXISTS extension_name; for each extension.

2

Create the books table with UUID and hstore columns

Create a table called books with three columns: id as UUID primary key, title as text, and metadata as hstore.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS hstore;

-- Create the books table below

Need a hint?

Use CREATE TABLE books (id UUID PRIMARY KEY, title TEXT, metadata hstore);

3

Insert a sample book with generated UUID and metadata

Insert a row into books with id generated by uuid_generate_v4(), title as 'The Great Gatsby', and metadata containing keys 'author' with value 'F. Scott Fitzgerald' and 'year' with value '1925'.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE books (
    id UUID PRIMARY KEY,
    title TEXT,
    metadata hstore
);

-- Insert the sample book below

Need a hint?

Use INSERT INTO books (id, title, metadata) VALUES (uuid_generate_v4(), 'The Great Gatsby', 'author=>"F. Scott Fitzgerald",year=>"1925"');

4

Query books with titles similar to a search term using pg_trgm

Write a SQL query to select all columns from books where the title is similar to the search term 'Great' using the % operator from pg_trgm.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE books (
    id UUID PRIMARY KEY,
    title TEXT,
    metadata hstore
);

INSERT INTO books (id, title, metadata) VALUES (
    uuid_generate_v4(),
    'The Great Gatsby',
    'author=>"F. Scott Fitzgerald",year=>"1925"'
);

-- Write the similarity query below

Need a hint?

Use SELECT * FROM books WHERE title % 'Great'; to find similar titles.