Postgres in depth · Chapter 30 of 65

Skills and tags

By the end of this chapter you can store a list in one column, search it, turn it into rows and build lists back from rows. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: The Postgres flavour
  • 5 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 wants a "Skills" page: each student sees the skills they are learning, built from the tags of their courses. Arjun wants to know which tags bring in students, so the next courses cover them. Both questions are about courses.tags, a column you have not looked at closely yet:

      title        │           tags
───────────────────┼───────────────────────────
 Kubernetes Basics │ {kubernetes,devops,cloud}

One value, three tags. = can only compare the whole list, LIKE on its text matches devops inside devops-tools too, and GROUP BY tags groups whole lists, not tags. Postgres has a type for lists, with its own operators and functions.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    backend.sql: the backend courses

    List the published courses tagged backend, with how many tags each one has.

    Return two columns: title and tag_count. Order by title.

    Careful: a tag must match exactly; a tag such as backend-ops would not count.

  2. Task 2

    tag-demand.sql: the demand behind each tag

    Arjun wants to know which tags bring students in. For every tag of a published course, count the enrollments in published courses carrying that tag.

    Return two columns: tag and enrollments. A tag whose courses have no enrollments shows 0. Order by enrollments, highest first, then by tag.

    Careful: the second test dataset has a published course nobody bought.

  3. Task 3

    skills.sql: each student's skills

    Kabir's Skills page: for every student who is not deleted and has at least one enrollment, the tags of all the courses they are enrolled in, as one list.

    Return two columns: name and skills (a text array, each tag once, in alphabetical order). Order by name.

  4. Task 4

    retag.sql: rename a tag

    The tag databases becomes sql. In every course tagged databases, sql takes its place in the list (the other tags keep their order).

    A course that already has sql simply loses databases, so no course ends up with sql twice. Courses without databases do not change.

  5. Task 5 · interview question

    Interview: related.sql, courses that share tags

    For the course page of React from Zero (slug react-from-zero): the other published courses that share at least one tag with it, and the tags they share.

    Return two columns: title and shared (a text array of the shared tags, in alphabetical order). Order by the number of shared tags, most first, then by title.

What you will learn

  • Arrays: the text[] type, literals, positions from 1, cardinality.
  • ANY, @>, &&: is a value in the list, does it hold all of these, any of these.
  • unnest: one row per element, so tags can be counted and joined.
  • array_agg and array functions: rows back into a list, and changing lists.

More in The Postgres flavour

Build "Skills and tags" 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.