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

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

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

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

Hint

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

Practice

1. What is the main purpose of the pg_trgm extension in PostgreSQL?

easy

A. To generate unique identifiers automatically

B. To store key-value pairs in a single column

C. To speed up text similarity and search operations

D. To manage user permissions and roles

Solution

1. Step 1: Understand the role of pg_trgm

   The pg_trgm extension provides functions and operators for determining the similarity of text based on trigram matching.

2. Step 2: Match purpose with options

   Among the options, only speeding up text similarity and search operations matches pg_trgm's purpose.

3. Final Answer:

   To speed up text similarity and search operations -> Option C

4. Quick Check:

   pg_trgm = text similarity speedup [OK]

Hint: Remember: pg_trgm is about text similarity and search [OK]

Common Mistakes:

• Confusing pg_trgm with uuid-ossp for ID generation
• Thinking hstore is for text search
• Assuming pg_trgm manages permissions

2. Which of the following is the correct SQL command to enable the uuid-ossp extension in PostgreSQL?

easy

A. CREATE EXTENSION uuid-ossp;

B. ENABLE EXTENSION uuid-ossp;

C. INSTALL EXTENSION uuid-ossp;

D. ADD EXTENSION uuid-ossp;

Solution

1. Step 1: Recall the syntax to enable extensions

   In PostgreSQL, extensions are enabled using the command CREATE EXTENSION extension_name;.

2. Step 2: Verify the correct command for uuid-ossp

   The correct command is CREATE EXTENSION uuid-ossp;. Other options are invalid SQL syntax.

3. Final Answer:

   CREATE EXTENSION uuid-ossp; -> Option A

4. Quick Check:

   Enable extension = CREATE EXTENSION [OK]

Hint: Use CREATE EXTENSION to enable PostgreSQL extensions [OK]

Common Mistakes:

• Using ENABLE or INSTALL instead of CREATE
• Forgetting the semicolon at the end
• Trying to add extension with ADD keyword

3. Given the following SQL commands, what will be the output of the last SELECT?

CREATE EXTENSION IF NOT EXISTS hstore;
SELECT 'a=>1, b=>2'::hstore -> 'a' AS value_a;

medium

A. Syntax error

B. 1

C. NULL

D. '1'

Solution

1. Step 1: Understand hstore key-value retrieval

   The operator -> when used with hstore returns the value as type hstore, which is text but includes quotes in output.

2. Step 2: Analyze the given hstore and query

   The hstore contains keys 'a' and 'b' with values '1' and '2' as text. Selecting -> 'a' returns the value as text with quotes, so output is '1'.

3. Final Answer:

   '1' -> Option D

4. Quick Check:

   hstore -> key returns quoted text value [OK]

Hint: hstore -> key returns text value shown quoted [OK]

Common Mistakes:

• Thinking output is unquoted 1
• Confusing -> with ->> operator
• Assuming NULL if key exists

4. You run the following command but get an error:

CREATE EXTENSION uuid-ossp;

What is the most likely cause and how to fix it?

medium

A. Extension is already installed; use DROP EXTENSION first

B. You lack superuser rights; ask admin to enable it

C. Syntax error; command should be ENABLE EXTENSION uuid-ossp;

D. Extension not supported in PostgreSQL

Solution

1. Step 1: Understand permissions for creating extensions

   Creating extensions like uuid-ossp requires superuser privileges in PostgreSQL.

2. Step 2: Identify cause of error

   If you get an error running CREATE EXTENSION uuid-ossp;, it is likely due to insufficient permissions, not syntax or availability.

3. Final Answer:

   You lack superuser rights; ask admin to enable it -> Option B

4. Quick Check:

   CREATE EXTENSION needs superuser [OK]

Hint: CREATE EXTENSION needs superuser rights [OK]

Common Mistakes:

• Assuming syntax error instead of permission issue
• Trying to DROP extension before creating
• Thinking extension is unsupported

5. You want to store user preferences as flexible key-value pairs in a PostgreSQL table. Which extension should you use and how do you insert a row with preferences for 'theme' as 'dark' and 'notifications' as 'enabled'?

hard

A. Use hstore; INSERT INTO users (prefs) VALUES ('"theme"=>"dark", "notifications"=>"enabled"');

B. Use uuid-ossp; INSERT INTO users (prefs) VALUES (uuid_generate_v4());

C. Use pg_trgm; INSERT INTO users (prefs) VALUES ('dark notifications');

D. Use hstore; INSERT INTO users (prefs) VALUES ('theme:dark, notifications:enabled');

Solution

1. Step 1: Identify extension for flexible key-value storage

   The hstore extension allows storing key-value pairs in one column, perfect for user preferences.

2. Step 2: Understand correct hstore insertion syntax

   hstore literals use the format '"key"=>"value"' pairs separated by commas inside single quotes.

3. Step 3: Match correct insertion command

   Use hstore; INSERT INTO users (prefs) VALUES ('"theme"=>"dark", "notifications"=>"enabled"'); uses correct hstore syntax for inserting preferences. Use hstore; INSERT INTO users (prefs) VALUES ('theme:dark, notifications:enabled'); uses incorrect syntax, and others use wrong extensions.

4. Final Answer:

   Use hstore; INSERT INTO users (prefs) VALUES ('"theme"=>"dark", "notifications"=>"enabled"'); -> Option A

5. Quick Check:

   hstore key=>value pairs need quotes and => [OK]

Hint: hstore stores key=>value pairs with quotes [OK]

Common Mistakes:

• Using uuid-ossp or pg_trgm for key-value storage
• Incorrect hstore syntax without => or quotes
• Confusing colon syntax with hstore format