UUID type and generation in PostgreSQL - Mini Project: Build & Apply

UUID Type and Generation in PostgreSQL

📖 Scenario: You are building a simple user management system where each user needs a unique identifier that is hard to guess. Instead of using regular numbers, you want to use UUIDs (Universally Unique Identifiers) to identify users securely.

🎯 Goal: Create a PostgreSQL table with a uuid column as the primary key. Then, configure the table to automatically generate a new UUID for each user when a record is inserted.

📋 What You'll Learn

Create a table named users with a column id of type uuid.

Set the id column as the primary key.

Add a column username of type text.

Configure the id column to automatically generate a UUID using the gen_random_uuid() function from the pgcrypto extension.

Enable the pgcrypto extension if not already enabled.

💡 Why This Matters

🌍 Real World

UUIDs are widely used in real applications to uniquely identify records in a way that is hard to guess or collide, especially in distributed systems.

💼 Career

Understanding how to use UUIDs in databases is important for backend developers and database administrators to design secure and scalable systems.

1

Enable the pgcrypto extension

Write the SQL command to enable the pgcrypto extension in PostgreSQL so you can use the gen_random_uuid() function.

PostgreSQL

-- Enable the pgcrypto extension
-- Your code here

Need a hint?

This extension provides the gen_random_uuid() function to generate UUIDs.

2

Create the users table with UUID primary key

Write the SQL command to create a table named users with two columns: id of type uuid as the primary key, and username of type text.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Create the users table with id as uuid primary key and username as text
-- Your code here

Need a hint?

Use uuid as the data type for id and set it as the primary key.

3

Set default UUID generation for id column

Alter the users table so that the id column automatically generates a UUID using gen_random_uuid() when a new row is inserted.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE users (
    id uuid PRIMARY KEY,
    username text
);
-- Alter the id column to set default value to gen_random_uuid()
-- Your code here

Need a hint?

Add DEFAULT gen_random_uuid() to the id column definition.

4

Insert a user without specifying id

Write an SQL insert statement to add a new user with username 'alice' into the users table without specifying the id. The id should be generated automatically.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    username text
);
-- Insert a new user with username 'alice' without specifying id
-- Your code here

Need a hint?

Do not include the id column in the insert statement so it uses the default UUID.