Postgres in depth · Chapter 14 of 65
Statuses and smart columns
By the end of this chapter you can give a column a fixed list of values with an enum, choose between an enum, a CHECK and a lookup table, share one rule between columns with a domain, and let Postgres compute a column for you. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Designing LCO properly
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Kabir is retiring jQuery Essentials: it should vanish from the catalogue but stay in the old students' dashboards. is_published can only say yes or no, and LCO now needs three states: draft, published and archived. Arjun sorts courses by level and gets "advanced" first, because text sorts alphabetically. Money has the same rule, "never negative, two decimals", written separately on four columns. And the website shows lesson lengths in minutes, worked out by hand in three different places, two of them wrong.
Each of these is a rule or a calculation that belongs in the database, written once.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
levels.sql: levels in the right order
Create an enum type course_level with the values 'beginner', 'intermediate' and 'advanced', in that order, and change courses.level to it. Every course keeps its level, the column stays required with the default 'beginner', and chapter 10's check on level (courses_level_check) is dropped: the enum does its job now.
- Task 2
status.sql: draft, published, archived
Replace courses.is_published with a status. Create an enum type course_status with 'draft', 'published' and 'archived', in that order. Add a column status of that type to courses: required, default 'draft'. Courses that were published get 'published'; the others stay 'draft'. Then drop is_published.
- Task 3
price.sql: one money type
Create a domain price_inr: numeric(10, 2), never negative. Change these four columns to it: courses.price, enrollments.price_paid, orders.total and order_items.price. Drop the check courses_price_not_negative from courses, since the domain has the rule now. Keep each column's other rules (required, and courses.price defaults to 0).
- Task 4 · interview question
Interview: duration.sql, minutes that compute themselves
Add a generated column duration_minutes to lessons: numeric, the lesson's duration_seconds divided by 60, rounded to 1 decimal place, stored. For example, 312 seconds is 5.2 minutes and 95 seconds is 1.6. Postgres computes it for every lesson, old and new, and keeps it up to date.
What you will learn
- Enums: a type with a fixed, ordered list of values.
- Enum, CHECK or lookup table: how to choose.
- Domains: a type with rules, shared by many columns.
- Generated columns: a column Postgres computes from the others.
More in Designing LCO properly
Build "Statuses and smart columns" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.