Arrays and enums

Small lists, fixed choices

A dish's allergens is a short list; an order's status is one of a fixed few. PostgreSQL has arrays for the first and enums for the second — simpler than extra tables when the data really is that simple.

text[] and CREATE TYPE … AS ENUM

CREATE TYPE order_status AS ENUM ('new', 'cooking', 'ready', 'served');

CREATE TABLE tickets (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  dish text NOT NULL,
  allergens text[] NOT NULL DEFAULT '{}',
  status order_status NOT NULL DEFAULT 'new'
);
INSERT INTO tickets (dish, allergens) VALUES
  ('Momo', ARRAY['gluten']),
  ('Peanut sadeko', ARRAY['peanut', 'sesame']),
  ('Tea', '{}');

SELECT dish FROM tickets WHERE 'peanut' = ANY (allergens);
SELECT dish, cardinality(allergens) AS n FROM tickets;
SELECT dish, unnest(allergens) AS allergen FROM tickets;

UPDATE tickets SET status = 'cooking' WHERE dish = 'Momo';
SELECT dish, status FROM tickets ORDER BY status DESC;

Use arrays for small, simple lists you don't need to join; otherwise use a table.
Add enum values later with ALTER TYPE order_status ADD VALUE 'cancelled'; (removing values is hard — choose wisely).

Small lists, fixed choices

text[] and CREATE TYPE … AS ENUM

The rest of this lesson