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.