Enumerated Types

Introduction to ENUM Types

ENUM (enumerated) types allow you to define a column with a static, predefined set of valid values. It's useful for fields like status, gender, payment mode, etc.

CREATE TYPE status AS ENUM ('active', 'inactive', 'suspended');

This ensures that only these three values can be stored.

Creating Tables with ENUM

Let's define a users table using the ENUM:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  account_status status
);

INSERT INTO users (name, account_status) VALUES
('Alice', 'active'),
('Bob', 'suspended'),
('Charlie', 'inactive');

Querying ENUM Values

-- Find all active users
SELECT * FROM users WHERE account_status = 'active';

-- Order by ENUM position
SELECT * FROM users ORDER BY account_status;

ENUMs are ordered based on the order you define them in.

Modifying ENUM Types

You can add new values, but you can't remove them easily.

-- Add new status
ALTER TYPE status ADD VALUE 'deleted';

Note: You can't change the order of ENUMs once defined.

ENUM vs CHECK Constraints

You might wonder: why not use CHECK constraints?

-- Using CHECK constraint
CREATE TABLE orders (
  id SERIAL,
  payment_method TEXT CHECK (payment_method IN ('cash', 'card', 'upi'))
);

While both approaches work:

Use Case Deep Dive

Imagine you're building a bug tracking system.

CREATE TYPE severity AS ENUM ('low', 'medium', 'high', 'critical');

CREATE TABLE bugs (
  id SERIAL,
  title TEXT,
  severity_level severity
);

INSERT INTO bugs (title, severity_level) VALUES
('Login button not working', 'high'),
('Minor UI glitch', 'low');

-- Find critical bugs:
SELECT * FROM bugs WHERE severity_level = 'critical';

Best Practices and Caveats

• Plan ENUMs carefully before production. Changing them is hard.
• ENUMs are best for small, stable value sets.
• Prefer CHECK constraints for values that change often.
• Reuse ENUM types across multiple tables for consistency.
• Don't store translated values (e.g., language strings) in ENUMs.