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

Need a 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

Need a 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

Need a 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

Need a hint?

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