Extensions (pg_trgm, uuid-ossp, hstore) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the pg_trgm extension in PostgreSQL?

The pg_trgm extension helps speed up text searches by breaking strings into smaller parts called trigrams, making it easier to find similar words quickly.

beginner

How does the uuid-ossp extension help in PostgreSQL?

The uuid-ossp extension provides functions to create universally unique identifiers (UUIDs), which are useful for generating unique keys that are hard to guess.

beginner

What is the hstore extension used for in PostgreSQL?

The hstore extension allows storing sets of key-value pairs within a single PostgreSQL column, useful for flexible or semi-structured data.

beginner

How do you enable the pg_trgm extension in a PostgreSQL database?

You enable it by running the SQL command: CREATE EXTENSION IF NOT EXISTS pg_trgm; inside your database.

beginner

Give an example of a function provided by the uuid-ossp extension.

One example is uuid_generate_v4(), which creates a random UUID version 4.

Which PostgreSQL extension helps speed up searches by breaking text into trigrams?

A pg_trgm

B uuid-ossp

C hstore

D postgis

The pg_trgm extension breaks text into trigrams to improve search speed.

What type of data does the hstore extension store?

A Images

B Key-value pairs

C UUIDs

D Geospatial data

Hstore stores key-value pairs inside a single column.

Which function is provided by uuid-ossp to generate a random UUID?

A uuid_generate_v1()

B uuid_make()

C uuid_create_random()

D uuid_generate_v4()

uuid_generate_v4() creates a random UUID version 4.

How do you enable an extension like pg_trgm in PostgreSQL?

A CREATE EXTENSION IF NOT EXISTS pg_trgm;

B ALTER DATABASE ADD EXTENSION pg_trgm;

C ENABLE EXTENSION pg_trgm;

D INSTALL EXTENSION pg_trgm;

You enable extensions using CREATE EXTENSION command.

Which extension would you use to generate unique identifiers for rows?

A pg_trgm

B hstore

C uuid-ossp

D citext

uuid-ossp provides functions to generate UUIDs for unique identifiers.

Explain the main uses of the PostgreSQL extensions pg_trgm, uuid-ossp, and hstore.

Think about text search, unique IDs, and flexible data storage.

Describe how to enable and use the pg_trgm extension for improving search performance.

Focus on enabling the extension and its effect on text search.

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