Postgres in depth · Chapter 60 of 65
The category tree
By the end of this chapter you can store a tree so that "everything under this", "everything above this" and "how deep" are single comparisons, and keep loose key-value data next to a row with hstore. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Extensions and graduation
- 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
LCO's categories are a tree: Web Development has Frontend and Backend, Frontend has React. Each row knows only its parent (categories.parent_id). Kabir wants the catalogue page to show how many courses each category has, counting everything below it, and a breadcrumb on every course page: Web Development > Frontend > React.
With only parent_id, every one of those questions is a recursive CTE (chapter 25), run again on every page view. ltree stores each row's whole path from the root, so the tree is in the row itself. And Priya has a second, smaller request: a quick way to see which columns differ between two rows, for the audit log. That is a trick of hstore.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
category-paths.sql: a path for every category
Add a column path of type ltree to categories and fill it for every category: the labels from the root down to the category, joined by dots. A category's label is its name in lowercase with every space turned into an underscore, so React's path is web_development.frontend.react. Then add a GiST index on path.
Install ltree in the file if it is missing: the Check runs it in a fresh copy of LCO's database.
Afterwards: every category has its full path, built from parent_id (never typed by hand: the other dataset has different ids), and categories has a GiST index on path.
- Task 2
subtree.sql: courses under each Web Development category
For each category in the Web Development subtree (Web Development itself included), count the published courses in that category or anywhere below it. The Check's copy has categories.path from task 1.
Return three columns: category (its name), path (as text) and courses. A category with no published course below it shows 0. Order by path.
- Task 3
breadcrumbs.sql: a breadcrumb for every course
Every published course page shows a breadcrumb: the names of its category's ancestors and of the category itself, from the root down, joined by a space, a greater-than sign and a space (for example Web Development > Frontend > React). The Check's copy has categories.path from task 1.
Return two columns: course (the title) and breadcrumb. Order by course.
- Task 4 · interview question
Interview: course-diff.sql, how two courses differ
An interviewer asks: without naming any column, list the columns in which the courses SQL Fundamentals and Postgres for Developers have different values.
Return three columns: key (the column name), sql_fundamentals (that course's value, as text) and postgres_for_developers (the other course's value, as text). Order by key.
Install hstore in the file if it is missing.
What you will learn
- ltree: a path type, its labels, and turning parent_id into paths.
- Tree operators: <@ (inside), @> (contains), ~ (matches a pattern), nlevel, subpath, lca.
- A GiST index for tree searches, and keeping paths current.
- hstore: key-value pairs, and comparing two rows with hstore(row).
More in Extensions and graduation
Build The category tree 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.