ENUM & Domains

-- ===== Create =====
CREATE TYPE order_status AS ENUM ('new', 'paid', 'shipped', 'cancelled');

-- ===== Use =====
CREATE TABLE orders (
  id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  status order_status NOT NULL DEFAULT 'new'
);

INSERT INTO orders DEFAULT VALUES;
UPDATE orders SET status = 'paid' WHERE id = ...;

-- ENFORCE values: any string outside the enum errors:
INSERT INTO orders (status) VALUES ('pending');
-- ERROR: invalid input value for enum order_status: 'pending'

-- ===== Ordering =====
-- Enum values are ordered as DEFINED, not alphabetical:
SELECT 'shipped'::order_status > 'new'::order_status;   -- true
ORDER BY status;   -- new, paid, shipped, cancelled

-- ===== Add a value =====
ALTER TYPE order_status ADD VALUE 'refunded';                       -- at end
ALTER TYPE order_status ADD VALUE 'pending' BEFORE 'paid';          -- in position
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'returned' AFTER 'shipped';

-- New values are visible immediately. Cannot be used in same transaction (PG <12).

-- ===== Rename a value =====
ALTER TYPE order_status RENAME VALUE 'cancelled' TO 'canceled';

-- ===== Remove a value =====
-- You CANNOT drop an enum value directly. The workaround:
--  1. Create a new ENUM without the value
--  2. ALTER TABLE ... ALTER COLUMN ... TYPE new_enum USING (...)
--  3. Drop the old enum
-- This is invasive; consider whether ENUM is the right tool.

-- ===== List values =====
SELECT enum_range(NULL::order_status);
-- {new,paid,shipped,cancelled,refunded}

SELECT unnest(enum_range(NULL::order_status));

-- ===== When ENUM wins =====
-- - Truly fixed set; rarely changes
-- - Performance: stored as 4-byte index, not string
-- - Self-documenting in the schema
-- - Sortable in defined order

-- ===== When a lookup table wins =====
-- - Set changes often (admin-editable)
-- - You need metadata per value (label, sort order, colour)
-- - You want to delete values
-- - Cross-DB portability matters

-- Pattern:
CREATE TABLE order_statuses (
  code        TEXT PRIMARY KEY,
  label       TEXT NOT NULL,
  sort_order  INT  NOT NULL,
  is_terminal BOOLEAN NOT NULL DEFAULT FALSE
);
INSERT INTO order_statuses (code, label, sort_order, is_terminal) VALUES
  ('new', 'New', 1, false),
  ('paid', 'Paid', 2, false),
  ('shipped', 'Shipped', 3, false),
  ('cancelled', 'Cancelled', 4, true);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  status TEXT NOT NULL REFERENCES order_statuses(code)
);

-- Trade: more flexible, slightly slower (FK check), extra table to manage.

-- ===== Patterns to internalise =====
-- - ENUM for fixed small sets (statuses, priorities, currencies)
-- - Lookup table for editable / metadata-bearing sets
-- - Add values rather than remove; use deprecation conventions
-- - Index the enum column when you filter on it heavily

-- ===== Pitfalls =====
-- - Removing values is painful; design conservatively
-- - Cross-DB portability lost (other engines treat enums differently)
-- - Ordering depends on definition order; rearranging breaks ORDER BY
-- - Mixing ENUM + lookup table in the same schema -> inconsistent UX

CREATE TYPE order_status AS ENUM ('pending','paid','shipped','refunded');
ALTER TABLE orders ADD COLUMN status order_status DEFAULT 'pending';