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.

Practice

1. What is the main purpose of using a UUID type in PostgreSQL?

easy

A. To store unique identifiers that avoid duplicates across tables or systems

B. To store large text data efficiently

C. To perform mathematical calculations faster

D. To create indexes automatically on a table

Solution

1. Step 1: Understand UUID purpose

   UUID stands for Universally Unique Identifier, designed to be unique across space and time.

2. Step 2: Identify its use in PostgreSQL

   PostgreSQL uses UUID to store unique IDs that avoid duplicates, especially in distributed systems.

3. Final Answer:

   To store unique identifiers that avoid duplicates across tables or systems -> Option A

4. Quick Check:

   UUID = Unique ID storage [OK]

Hint: UUIDs are for unique IDs, not for text or math [OK]

Common Mistakes:

• Confusing UUID with text or numeric types
• Thinking UUID speeds up math operations
• Assuming UUID auto-creates indexes

2. Which of the following is the correct way to generate a random UUID in PostgreSQL?

easy

A. SELECT uuid_generate_v4();

B. SELECT random_uuid();

C. SELECT gen_random_uuid();

D. SELECT create_uuid();

Solution

1. Step 1: Recall PostgreSQL UUID functions

   PostgreSQL's pgcrypto extension provides gen_random_uuid() to generate random UUIDs.

2. Step 2: Check function names

   uuid_generate_v4() is from another extension (uuid-ossp), not default. The others are invalid.

3. Final Answer:

   SELECT gen_random_uuid(); -> Option C

4. Quick Check:

   Random UUID function = gen_random_uuid() [OK]

Hint: Use gen_random_uuid() with pgcrypto extension [OK]

Common Mistakes:

• Using uuid_generate_v4() without enabling uuid-ossp
• Typing random_uuid() which does not exist
• Confusing function names

3. Given the table creation and insert statements below, what will be the output of the SELECT query?

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE users (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT);
INSERT INTO users (name) VALUES ('Alice'), ('Bob');
SELECT id, name FROM users ORDER BY name;

medium

A. Two rows with NULL ids for Alice and Bob

B. Two rows with unique UUIDs for Alice and Bob, ordered by name

C. Syntax error because gen_random_uuid() is not allowed in DEFAULT

D. One row only because UUID duplicates cause conflict

Solution

1. Step 1: Understand table and insert behavior

   The table uses gen_random_uuid() as default for id. Inserting names without specifying id generates unique UUIDs automatically.

2. Step 2: Analyze SELECT output

   The SELECT query returns all rows ordered by name, showing unique UUIDs for Alice and Bob.

3. Final Answer:

   Two rows with unique UUIDs for Alice and Bob, ordered by name -> Option B

4. Quick Check:

   Default UUID generates unique IDs [OK]

Hint: DEFAULT gen_random_uuid() auto-fills unique IDs [OK]

Common Mistakes:

• Expecting NULL ids when default is set
• Thinking gen_random_uuid() causes syntax error
• Assuming UUID duplicates block inserts

4. You run the following commands but get an error: ERROR: function gen_random_uuid() does not exist. What is the most likely fix?

SELECT gen_random_uuid();

medium

A. Change the query to SELECT random_uuid();

B. Use uuid_generate_v4() instead without any extension

C. Restart PostgreSQL server to refresh functions

D. Enable the pgcrypto extension using CREATE EXTENSION pgcrypto;

Solution

1. Step 1: Identify cause of missing function

   The error means gen_random_uuid() is not available because the pgcrypto extension is not enabled.

2. Step 2: Apply correct fix

   Enabling pgcrypto with CREATE EXTENSION pgcrypto; adds the function.

3. Final Answer:

   Enable the pgcrypto extension using CREATE EXTENSION pgcrypto; -> Option D

4. Quick Check:

   Missing function = enable pgcrypto [OK]

Hint: Enable pgcrypto extension to use gen_random_uuid() [OK]

Common Mistakes:

• Trying uuid_generate_v4() without enabling uuid-ossp
• Restarting server unnecessarily
• Using random_uuid() which does not exist

5. You want to create a table that stores user sessions with a UUID primary key generated automatically, but also want to insert a specific UUID sometimes. Which table definition and insert statement is correct?

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE sessions (
  session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id INT
);

-- Insert with automatic UUID
INSERT INTO sessions (user_id) VALUES (101);

-- Insert with specific UUID
INSERT INTO sessions (session_id, user_id) VALUES ('550e8400-e29b-41d4-a716-446655440000', 102);

hard

A. This setup works correctly, allowing both automatic and manual UUID insertion

B. You cannot insert a specific UUID if DEFAULT is set for the column

C. You must disable the default to insert specific UUIDs

D. The UUID must be generated outside PostgreSQL and inserted as text

Solution

1. Step 1: Understand DEFAULT with manual insert

   Setting DEFAULT to gen_random_uuid() means if no value is given, PostgreSQL generates one automatically.

2. Step 2: Check manual UUID insertion

   When you specify session_id explicitly, PostgreSQL uses that value instead of the default.

3. Step 3: Confirm correctness

   This allows both automatic UUID generation and manual UUID insertion in the same table.

4. Final Answer:

   This setup works correctly, allowing both automatic and manual UUID insertion -> Option A

5. Quick Check:

   DEFAULT allows manual override [OK]

Hint: DEFAULT UUID generates only if no value given [OK]

Common Mistakes:

• Thinking DEFAULT blocks manual inserts
• Trying to disable default for manual UUIDs
• Assuming UUID must be generated outside PostgreSQL