Extensions (pg_trgm, uuid-ossp, hstore) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Using pg_trgm to find similar words

Given a table products with a column name, which query will return product names similar to 'apple' using the pg_trgm extension?

PostgreSQL

SELECT name FROM products WHERE name % 'apple';

A SELECT name FROM products WHERE name % 'apple';

B SELECT name FROM products WHERE similarity(name, 'apple') > 0.3;

C SELECT name FROM products WHERE name LIKE '%apple%';

D SELECT name FROM products WHERE name = 'apple';

💡 Hint

The pg_trgm extension provides the % operator for similarity search.

The % operator is provided by pg_trgm to find strings similar to the given string. Option A uses this operator correctly. Option A uses similarity function but without ordering or threshold filtering properly. Option A uses LIKE which is substring match, not trigram similarity. Option A is exact match.

📝 Syntax

intermediate

Correct syntax to generate a UUID using uuid-ossp

Which of the following SQL statements correctly generates a version 4 UUID using the uuid-ossp extension?

A SELECT generate_uuid_v4();

B SELECT uuid_generate_v4();

C SELECT uuid_v4_generate();

D SELECT generate_v4_uuid();

💡 Hint

The uuid-ossp extension provides functions starting with 'uuid_generate_'.

The correct function to generate a version 4 UUID is uuid_generate_v4(). Other options are invalid function names and will cause errors.

❓ query_result

advanced

Querying hstore data for a specific key

Given a table settings with a column config of type hstore, which query returns all rows where the key 'theme' has the value 'dark'?

A SELECT * FROM settings WHERE config ->> 'theme' = 'dark';

B SELECT * FROM settings WHERE config -> 'theme' = 'dark';

C SELECT * FROM settings WHERE config['theme'] = 'dark';

D SELECT * FROM settings WHERE config @> 'theme=>dark';

💡 Hint

The @> operator checks if the hstore contains a key-value pair.

Option D uses the @> operator to check if the hstore contains the pair 'theme'=>'dark'. Option D returns a hstore value, not text, so comparison fails. Option D returns text but the operator ->> is for json, not hstore. Option D syntax is invalid for hstore.

❓ optimization

advanced

Improving search performance with pg_trgm

You want to speed up similarity searches on the description column of a documents table using pg_trgm. Which index type is best to create?

A CREATE INDEX trgm_idx ON documents USING gist (description gist_trgm_ops);

B CREATE INDEX trgm_idx ON documents USING btree (description);

C CREATE INDEX trgm_idx ON documents USING gin (description gin_trgm_ops);

D CREATE INDEX trgm_idx ON documents USING hash (description);

💡 Hint

pg_trgm works best with GIN or GiST indexes using gin_trgm_ops or gist_trgm_ops.

Option C creates a GIN index with gin_trgm_ops which is optimized for trigram similarity searches. Btree and hash indexes do not support trigram operators. GiST with gist_trgm_ops is possible but GIN is generally faster for this use case.

🔧 Debug

expert

Debugging hstore key retrieval error

You run this query: SELECT config ->> 'language' FROM settings; but get an error: operator does not exist: hstore ->> unknown. What is the cause?

PostgreSQL

SELECT config ->> 'language' FROM settings;

A The hstore extension is not enabled in the database.

B The ->> operator is not defined for hstore; use -> instead.

C The config column is not of type hstore but json or jsonb.

D The ->> operator requires the key to be cast to text explicitly.

💡 Hint

Check if the hstore extension is installed and enabled.

The error indicates the operator ->> for hstore is missing, which usually means the hstore extension is not enabled. Option A is wrong because ->> is valid for hstore. Option A would cause a different error or work with json operators. Option A is unnecessary as the key is already text.

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