Postgres in depth · Chapter 34 of 65

Views

By the end of this chapter you can give a query a name that works like a table, let people change data through it safely, store an expensive result and refresh it, and use a view to share some data without the rest. 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
  • 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

The catalogue query (published courses with their instructor's name) is now pasted into the website, the mobile app and three of Arjun's reports. When Kabir asked for the level on every card, Priya had to find and change all five copies, and missed one. The marketing intern may edit free courses only, and must never give one a price by accident. The dashboard's course statistics take a while to compute and are asked for on every page load. And Arjun's analyst account should see revenue per instructor, not every order with every student's name.

Copying a query does not scale, and the table rights you know so far are all or nothing: whoever can read the totals can read the orders.

What you build: 4 tasks

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

  1. Task 1

    catalogue.sql: one catalogue for every app

    Create a view named catalogue of the published courses, so the website, the app and the reports all use one definition.

    Its columns, in this order: title, slug, instructor (the instructor's name), level and price. The Check publishes a draft course afterwards: it must appear in the view at once.

  2. Task 2

    free-courses.sql: edit free courses, keep them free

    The marketing intern may edit free courses through a view named free_courses: the courses whose price is 0, with the columns id, title, slug, price and status, in this order.

    Renaming a course through the view must work; giving a course a price through the view must be refused.

  3. Task 3

    course-stats.sql: dashboard numbers, stored

    Create a materialized view named course_stats with one row per published course: slug, title, students (its enrollments), revenue (the sum of price_paid, 0 when none) and avg_rating (the average review rating rounded to 2 decimals, NULL when none).

    It must be possible to refresh it concurrently, so the dashboard never waits. A published course nobody bought shows 0 students.

  4. Task 4 · interview question

    Interview: instructor-revenue.sql, totals without the orders

    Arjun's analyst role may see revenue per instructor, but must not read orders. Create a view named instructor_revenue with two columns: instructor (the name) and revenue (the sum of order_items.price on paid orders of their courses, 0 for an instructor with none). Every instructor appears.

    Then let the role analyst read the view, and nothing else.

What you will learn

  • Views: a named query that you SELECT from like a table.
  • Changing a view: CREATE OR REPLACE VIEW, its rules, and views that depend on views.
  • Updatable views and WITH CHECK OPTION: writing through a view without escaping it.
  • Materialized views: a stored result, REFRESH, and CONCURRENTLY.

More in The Postgres flavour

Build "Views" 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.