Extensions (pg_trgm, uuid-ossp, hstore) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to enable the pg_trgm extension in PostgreSQL.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS [1];

Drag options to blanks, or click blank then click option'

Apostgis

Buuid-ossp

Chstore

Dpg_trgm

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing 'uuid-ossp' which is for UUID generation.

Choosing 'hstore' which is for key-value storage.

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

2fill in blank

medium

Complete the code to create a UUID using the uuid-ossp extension.

PostgreSQL

SELECT [1]() AS new_uuid;

Drag options to blanks, or click blank then click option'

Auuid_generate_v4

Bgen_random_uuid

Cuuid_create

Dgenerate_uuid

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'gen_random_uuid' which is from the pgcrypto extension, not uuid-ossp.

Using incorrect function names like 'uuid_create'.

The function uuid_generate_v4() creates a random UUID version 4 using the uuid-ossp extension.

3fill in blank

hard

Fix the error in the code to store a key-value pair using the hstore extension.

PostgreSQL

INSERT INTO settings (data) VALUES ([1]('theme', 'dark'));

Drag options to blanks, or click blank then click option'

Ahstore

Bjsonb

Carray

Dtext

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'jsonb' which is a different data type.

Using 'array' or 'text' which are not key-value constructors.

The hstore function creates a key-value pair for the hstore data type.

4fill in blank

hard

Fill both blanks to create a trigram index on the column 'description' in table 'products'.

PostgreSQL

CREATE INDEX [1] ON products USING [2] (description);

Drag options to blanks, or click blank then click option'

Aidx_products_description_trgm

Bidx_products_description_gin

Cgin

Dgist

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'gist' index type which is less common for pg_trgm.

Using generic index names that don't describe the column.

The index name is usually descriptive. The gin index type is used with pg_trgm for trigram indexing.

5fill in blank

hard

Fill both blanks to query hstore data for the value of key 'color' in the 'attributes' column.

PostgreSQL

SELECT attributes[1] 'color' AS color_value, attributes-> 'color' AS hstore_value FROM products WHERE attributes[2] 'color';

Drag options to blanks, or click blank then click option'

A->

B->>

C?

D@>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '->' instead of '->>' to get text value.

Using '@>' which checks containment, not key existence.

Mixing up the operators for key existence and value extraction.

The operator ->> extracts the value as text from an hstore column. The -> operator extracts the value as hstore. The ? operator checks if a key exists.

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