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.