UUID type and generation in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this UUID generation query?

Consider the following PostgreSQL query that generates a UUID using the gen_random_uuid() function. What is the expected output type of the query?

PostgreSQL

SELECT gen_random_uuid();

A A single row with a random integer value

B Multiple rows each containing a UUID value

C A single row with a UUID value in standard 8-4-4-4-12 hexadecimal format

D An error because <code>gen_random_uuid()</code> is not a valid function

💡 Hint

Think about what gen_random_uuid() returns and how many rows the query produces.

The gen_random_uuid() function returns a single UUID value in the standard format as a single row result.

🧠 Conceptual

intermediate

Which UUID version does gen_random_uuid() generate?

In PostgreSQL, the gen_random_uuid() function generates UUIDs. Which version of UUID does it produce?

A Version 4 (randomly generated UUID)

B Version 3 (name-based using MD5)

C Version 1 (time-based UUID)

D Version 5 (name-based using SHA-1)

💡 Hint

Consider the randomness aspect of the UUID generated by gen_random_uuid().

gen_random_uuid() generates a Version 4 UUID, which is randomly generated.

📝 Syntax

advanced

Which query correctly creates a table with a UUID primary key that auto-generates UUIDs?

You want to create a PostgreSQL table named users with a user_id column as a UUID primary key that automatically generates a UUID for each new row. Which query is correct?

A CREATE TABLE users (user_id UUID PRIMARY KEY DEFAULT gen_random_uuid());

B CREATE TABLE users (user_id UUID PRIMARY KEY DEFAULT uuid_generate_v4());

C CREATE TABLE users (user_id UUID PRIMARY KEY DEFAULT random_uuid());

D CREATE TABLE users (user_id UUID PRIMARY KEY DEFAULT uuid());

💡 Hint

Check the exact function name for generating random UUIDs in PostgreSQL's pgcrypto extension.

The correct function to generate random UUIDs in PostgreSQL's pgcrypto extension is gen_random_uuid(). The other functions either do not exist or are incorrect.

❓ optimization

advanced

Which approach improves UUID indexing performance in PostgreSQL?

You have a table with a UUID primary key generated by gen_random_uuid(). UUIDs are random and can cause index bloat. Which approach can improve index performance?

A Disable indexing on the UUID column

B Use UUID version 1 (time-based) instead of version 4 to generate UUIDs

C Store UUIDs as text instead of UUID type

D Create a B-tree index on the UUID column without changes

💡 Hint

Think about how the order of UUID values affects index performance.

UUID version 1 includes a timestamp, so values are roughly ordered, reducing index fragmentation and improving performance compared to random UUIDs.

🔧 Debug

expert

Why does this query fail with "function gen_random_uuid() does not exist" error?

You run this query: SELECT gen_random_uuid(); but get the error: function gen_random_uuid() does not exist. What is the most likely cause?

A The query requires superuser privileges to run

B The function name is misspelled; it should be <code>generate_random_uuid()</code>

C The UUID type is not supported in this PostgreSQL version

D The pgcrypto extension is not installed or enabled in the database

💡 Hint

Check if the required extension for UUID generation is available and enabled.

The gen_random_uuid() function is provided by the pgcrypto extension. If this extension is not installed or enabled, the function will not exist.