UUID type and generation in PostgreSQL

Introduction

UUIDs are unique codes used to identify things without confusion. They help keep data safe and easy to find.

When you want to give each user a unique ID that won't repeat.

When combining data from different places and need unique keys.

When you want IDs that are hard to guess for security reasons.

When you want to avoid using numbers that can be easily predicted.

Syntax

PostgreSQL

CREATE TABLE table_name (
  id UUID DEFAULT gen_random_uuid(),
  other_columns ...
);

-- Or generate UUID in a query:
SELECT gen_random_uuid();

You need the pgcrypto extension to use gen_random_uuid().

UUIDs are 128-bit values shown as 36 characters including dashes.

Examples

This creates a table where each user gets a unique UUID automatically.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  username TEXT
);

Inserts two users; their user_id will be unique UUIDs generated automatically.

PostgreSQL

INSERT INTO users (username) VALUES ('alice');
INSERT INTO users (username) VALUES ('bob');

This query generates a new random UUID without inserting it anywhere.

PostgreSQL

SELECT gen_random_uuid();

Sample Program

This example creates a products table with UUIDs as IDs, inserts two products, and shows their IDs and names.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE products (
  product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT
);

INSERT INTO products (name) VALUES ('Pen');
INSERT INTO products (name) VALUES ('Notebook');

SELECT product_id, name FROM products;

Important Notes

UUIDs are great for distributed systems where many places create data.

Remember to enable pgcrypto extension before using gen_random_uuid().

UUIDs take more space than simple numbers but give better uniqueness.

Summary

UUIDs are unique identifiers that help avoid duplicates.

PostgreSQL uses gen_random_uuid() to create UUIDs.

Enable pgcrypto extension to use UUID generation functions.