Deviopath › PostgreSQL

Priority as an Enum

Hard · DDL

An enum type is a fixed list of labels: CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');. Enum values sort in the order you listed them, not alphabetically, so 'ok' < 'happy' is true for a mood.

The tickets.priority column is plain text, and some values are messy (extra spaces, capitals).

1. Create an enum type ticket_priority with the labels low, medium, high, urgent (in that order). 2. Change tickets.priority to ticket_priority. Clean the old values while converting with ALTER COLUMN ... TYPE ... USING expression (lowercase them and trim the spaces). 3. Give the column the default 'medium'.

The check inserts a ticket without a priority and lists tickets with priority >= 'medium', most urgent first.

Next: Orders Between Two Dates