ENUM types in PostgreSQL - Mini Project: Build & Apply

Using ENUM Types in PostgreSQL

📖 Scenario: You are building a simple database to store information about customer orders. Each order has a status that can only be one of a few fixed values.

🎯 Goal: Create an ENUM type called order_status with specific values, then create a table orders using this ENUM type for the status column.

📋 What You'll Learn

Create an ENUM type named order_status with values 'pending', 'shipped', and 'delivered'.

Create a table named orders with columns order_id (integer primary key), customer_name (text), and status using the order_status ENUM type.

Insert one sample row into the orders table with order_id 1, customer_name 'Alice', and status 'pending'.

Update the status of the order with order_id 1 to 'shipped'.

💡 Why This Matters

🌍 Real World

ENUM types are useful when you have a column that should only allow a fixed set of values, like order statuses, user roles, or product categories.

💼 Career

Knowing how to use ENUM types helps you design databases that enforce data integrity and make your queries simpler and safer.

1

Create the ENUM type order_status

Write a SQL statement to create an ENUM type called order_status with the values 'pending', 'shipped', and 'delivered'.

PostgreSQL

-- Create ENUM type order_status with values 'pending', 'shipped', 'delivered'
-- Your code here

Hint

Use CREATE TYPE followed by the type name and AS ENUM with the list of values in parentheses.

2

Create the orders table using the ENUM type

Write a SQL statement to create a table named orders with columns: order_id as integer primary key, customer_name as text, and status using the ENUM type order_status.

PostgreSQL

CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered');
-- Create table orders with order_id, customer_name, and status columns
-- Your code here

Hint

Use CREATE TABLE with the specified columns and use the ENUM type order_status for the status column.

3

Insert a sample row into the orders table

Write a SQL statement to insert a row into the orders table with order_id 1, customer_name 'Alice', and status 'pending'.

PostgreSQL

CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered');
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    status order_status
);
-- Insert one row with order_id 1, customer_name 'Alice', and status 'pending'
-- Your code here

Hint

Use INSERT INTO orders specifying the columns and values exactly as given.

4

Update the status of the order to 'shipped'

Write a SQL statement to update the status column to 'shipped' for the row where order_id is 1 in the orders table.

PostgreSQL

CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered');
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    status order_status
);
INSERT INTO orders (order_id, customer_name, status) VALUES (1, 'Alice', 'pending');
-- Update the status to 'shipped' for order_id 1
-- Your code here

Hint

Use UPDATE orders SET status = 'shipped' WHERE order_id = 1; to change the status.

Practice

1. What is the main purpose of using ENUM types in PostgreSQL?

easy

A. To restrict a column to a fixed set of allowed values

B. To store large text data efficiently

C. To create temporary tables

D. To index numeric columns faster

Solution

1. Step 1: Understand ENUM type purpose

   ENUM types define a list of allowed values for a column, ensuring data consistency.

2. Step 2: Compare with other options

   Other options describe unrelated features like text storage, temporary tables, or indexing.

3. Final Answer:

   To restrict a column to a fixed set of allowed values -> Option A

4. Quick Check:

   ENUM = fixed allowed values [OK]

Hint: ENUM limits values to a fixed list, ensuring consistency [OK]

Common Mistakes:

• Thinking ENUM stores large text data
• Confusing ENUM with temporary tables
• Assuming ENUM improves indexing speed

2. Which of the following is the correct syntax to create an ENUM type named mood with values 'happy', 'sad', and 'neutral'?

easy

A. CREATE ENUM TYPE mood AS ('happy', 'sad', 'neutral');

B. CREATE ENUM mood ('happy', 'sad', 'neutral');

C. CREATE TYPE mood ENUM ['happy', 'sad', 'neutral'];

D. CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');

Solution

1. Step 1: Recall ENUM creation syntax

   The correct syntax is CREATE TYPE name AS ENUM (values); with values in parentheses and single quotes.

2. Step 2: Check each option

   CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral'); matches the correct syntax exactly. Others have wrong keywords or brackets.

3. Final Answer:

   CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral'); -> Option D

4. Quick Check:

   CREATE TYPE ... AS ENUM (values) [OK]

Hint: Use CREATE TYPE name AS ENUM (values) syntax [OK]

Common Mistakes:

• Using CREATE ENUM instead of CREATE TYPE
• Using square brackets instead of parentheses
• Omitting AS keyword

3. Given the ENUM type and table below, what will be the result of the query?

CREATE TYPE colors AS ENUM ('red', 'green', 'blue');
CREATE TABLE items (id SERIAL PRIMARY KEY, color colors);
INSERT INTO items (color) VALUES ('green'), ('blue'), ('red');

SELECT color FROM items ORDER BY color;

medium

A. red, blue, green

B. blue, green, red

C. green, blue, red

D. red, green, blue

Solution

1. Step 1: Understand ENUM ordering

   ENUM values are ordered by their declaration order: 'red' < 'green' < 'blue'.

2. Step 2: Apply ORDER BY on color column

   Ordering by color sorts rows as per ENUM order, so 'red', 'green', 'blue'.

3. Final Answer:

   red, green, blue -> Option A

4. Quick Check:

   ENUM order = declaration order [OK]

Hint: ENUM sorts by declared order, not alphabetically [OK]

Common Mistakes:

• Assuming alphabetical order instead of ENUM order
• Confusing insertion order with sort order
• Expecting default text sorting

4. What is wrong with the following SQL code?

CREATE TYPE status AS ENUM ('new', 'in_progress', 'done');
CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  task_status status DEFAULT 'pending'
);

medium

A. ENUM types cannot be used as column types

B. Missing semicolon after CREATE TYPE statement

C. The default value 'pending' is not in the ENUM list

D. The SERIAL keyword is deprecated

Solution

1. Step 1: Check ENUM values and default

   ENUM 'status' has values 'new', 'in_progress', 'done'. Default 'pending' is not listed.

2. Step 2: Understand default value constraints

   Default must be one of ENUM values; otherwise, it causes an error.

3. Final Answer:

   The default value 'pending' is not in the ENUM list -> Option C

4. Quick Check:

   Default must be ENUM member [OK]

Hint: Default must be one of ENUM values [OK]

Common Mistakes:

• Assuming any string can be default
• Thinking ENUM can't be column type
• Ignoring missing semicolon errors

5. You want to add a new value 'archived' to an existing ENUM type status with values ('new', 'in_progress', 'done'). Which statement correctly adds 'archived' after 'done'?

hard

A. ALTER TYPE status ADD VALUE 'archived' BEFORE 'in_progress';

B. ALTER TYPE status ADD VALUE 'archived' AFTER 'done';

C. ALTER TYPE status ADD VALUE 'archived' BEFORE 'done';

D. ALTER TYPE status MODIFY VALUE 'archived' AFTER 'done';

Solution

1. Step 1: Recall how to add ENUM values

   PostgreSQL uses ALTER TYPE ... ADD VALUE 'new_value' [BEFORE|AFTER existing_value] syntax.

2. Step 2: Identify correct position

   To add 'archived' after 'done', use AFTER 'done'.

3. Step 3: Check options

   ALTER TYPE status ADD VALUE 'archived' AFTER 'done'; matches correct syntax and position. Others use BEFORE (wrong position) or invalid keywords.

4. Final Answer:

   ALTER TYPE status ADD VALUE 'archived' AFTER 'done'; -> Option B

5. Quick Check:

   ALTER TYPE ADD VALUE ... AFTER ... [OK]

Hint: Use ALTER TYPE ADD VALUE 'val' AFTER 'existing' to position [OK]

Common Mistakes:

• Using MODIFY instead of ADD VALUE
• Placing new value BEFORE wrong existing value
• Using wrong position like BEFORE 'done'