09-05 closed by promising "the arsenal that makes everything above maintainable", and the first tool in that arsenal is the simplest of them all: giving a query a name. A view is exactly that —a query stored in the database under a name— and it doesn't look like much until you count how many times in this course you've copied the same four-table JOIN and the same quantity * unit_price * (1 - discount) expression.

In this lesson you'll see what a view is and what it is not (it doesn't store data: it stores the definition), how to create it, replace it and drop it with their exact rules, the five real reasons people use them —with a GreenStore case for each one, including the soft delete 05-04 left pending—, when PostgreSQL lets you write through a view and what WITH CHECK OPTION is, why a view isn't a cache and what happens when you nest them, and the materialized views 08-04 pointed here for: the ones that do store data, with their REFRESH and their price.

1. What a view is and what it isn't
2. CREATE VIEW, CREATE OR REPLACE VIEW, DROP VIEW
3. The five reasons, with one case each
4. Updatable views and WITH CHECK OPTION
5. Performance: a view is expanded, not cached
6. Materialized views
7. Metadata: where views live
8. Common Mistakes and Tips
9. Exercises
10. Conclusion

A view is a SELECT query stored in the schema under a name. When you query it, the engine replaces the name with its definition and runs the resulting query against the real tables.

That diagram is the whole lesson in one picture: the view disappears before it runs. It's syntactic sugar with a name and permissions of its own, not a storage layer.

(Every view in this lesson —v_active_products, v_sales_detail, v_sales_by_category, v_customers_public, mv_monthly_sales— is an example object for this lesson: none of them is part of the canonical GreenStore schema from 01-06.)

The three operations and their rules:

That OR REPLACE restriction is the one that wastes the most time:

Adding at the end, on the other hand, is fine: SELECT p.id, p.name, p.category_id, p.price, p.stock, p.supplier_id goes through without a complaint.

The reason is the same one that made an ALTER TABLE delicate back in 05-06: other queries and other views depend on the position and the type of every column. To change the shape of a view you have to do DROP VIEW + CREATE VIEW, and that forces you to recreate everything that depended on it too. ALTER VIEW exists, but only for peripheral things: renaming the view or a column, changing owner or schema.

You've been writing these four JOIN lines since module 3. Write them once:

And from then on, a business query reads like a sentence:

(First 5 of 47 rows.) And SELECT COUNT(*), ROUND(SUM(amount), 2) FROM v_sales_detail returns 47 and 727.95: the course's canonical figures, now one SELECT away.

This reason matters more than the previous one and is harder to see. The view holds a business decision written down exactly once: the amount of a line is quantity * unit_price * (1 - discount), rounded to two decimals, excluding shipping costs. If that formula lives copied across fourteen reports, sooner or later three of them will forget the (1 - discount) and management will get three different revenue figures in the same meeting.

A view is where a metric gets defined. The aggregated version, built on the previous one:

The five categories with sales and their canonical figures. Supplements doesn't show up because the JOIN on sales discards it (04-05); to see it with a 0 you'd have to start from categories with a LEFT JOIN. Notice too that this view is built on another view: that's legal and very convenient, with the caveat in section 5.

This is where 05-04's promise gets kept. Soft deletion with active = FALSE had one huge drawback: you have to remember to write WHERE active everywhere, and the day somebody forgets, a discontinued product will show up in the shop.

The v_active_products view from section 2 returns 19 of the 20 rows in products: the Spirulina capsules (product 20, active = FALSE) aren't there, and there's no way for them to slip through. The operating rule is simple: the application queries the view; only catalogue maintenance touches the table. The filter stops being something to remember and becomes something that's just there.

In 05-06 you saw the expand/contract pattern: to rename a column without downtime you add the new one, write both for a while and retire the old one. Now suppose products.name gets renamed to trade_name. Every application query pointing at products.name breaks; the ones pointing at the view don't, because you only have to redefine it with SELECT p.trade_name AS name, ... and the change is absorbed in there.

The view acts as a stable contract: from the outside there's still a name column, and inside the schema is free to evolve. It's the same idea as an interface in programming, and it's why many teams expose only views to reports and BI tools, never tables.

A view can leave out columns and rows. customers has email, which is personal data, and referred_by_id, which is internal commercial information:

Whoever queries it will never see an email address, because it isn't in the view. The same goes for rows: WHERE country = 'Spain' in the definition creates a view that only shows the domestic market.

This is a security tool, and here we're only mentioning it. For it to be worth anything you have to revoke the permission on the table and grant it on the view —GRANT SELECT ON v_customers_public TO ...— and that, together with roles and row-level access control, is the subject of 11-03.

A reasonable surprise: sometimes you can write through a view. PostgreSQL considers it automatically updatable —INSERT, UPDATE and DELETE just work— when it meets all of these conditions:

UPDATE v_active_products SET price = 13.00 WHERE id = 1; answers UPDATE 1, and the change has gone to the products table. And now the interesting problem. Let's add active to the view —remember: adding at the end is allowed— so we can write to it:

And the olive oil has just vanished from the view. Through a window, you've written a row that the window no longer shows: the UPDATE says it touched one row, but querying it again from here is impossible. That's called going out the back door, and you close it like this:

With WITH CHECK OPTION, every row inserted or modified must still satisfy the view's WHERE:

It has two variants: WITH LOCAL CHECK OPTION checks only this view's condition, and WITH CASCADED CHECK OPTION checks this one and that of every view it's built on —which is what applies if you say nothing.

And for views that aren't automatically updatable —v_sales_detail, for instance— PostgreSQL offers two ways out: an INSTEAD OF trigger, which intercepts the write and decides by hand which tables to touch, or the rule system (CREATE RULE), older and discouraged. The modern way is the trigger, and that's exactly what you'll see in 10-05.

This is the most expensive misunderstanding in the lesson: a view stores nothing and saves not a single microsecond of work. The planner replaces it with its definition and optimizes the whole thing.

The good news is that the substitution is intelligent: filters from the outside get pushed inwards.

Look at the Filter: country = 'France': it has sunk all the way down to the scan of customers. The view hasn't materialized 47 rows to filter them afterwards; the filter is part of the plan. And in that plan the word v_sales_detail appears nowhere, which is exactly the point.

The risk shows up with nesting. v_sales_by_category builds on v_sales_detail, which joins four tables: two levels still read fine. But in databases with a few years on them it's common to find a view on a view on a view, each with its JOINs and its just-in-case LEFT JOINs; once they're all expanded, the planner is faced with a twenty-table query it doesn't know how to reorder —beyond join_collapse_limit, 8 by default, it stops trying combinations— and picks a mediocre plan. The symptoms are unmistakable: a query that asks for three columns takes four seconds and its EXPLAIN ANALYZE is full of tables you never asked for.

Three practical rules: two levels of nesting at most (if you need more, the intermediate level probably wants to be a CTE inside the final query, 10-02, or a materialized view); when a view is slow, EXPLAIN the query that uses it, not the view on its own (08-05); and no ORDER BY in the definition, since it isn't guaranteed to survive the expansion.

This is where 08-04's promise gets kept. A materialized view does store the rows on disk: it's the result of a query frozen in time.

The answer is SELECT 12, not CREATE VIEW: it has run the query and written its 12 rows.

(First 4 of 12 rows; the full series, from 2025-03 to 2026-02, is the one you'll use in 10-03.) It now reads from disk without touching orders or order_lines. And with that comes the drawback: if order 21 arrives tomorrow, this table will keep saying the same thing. The data stays as it was until somebody refreshes it.

That requirement isn't a whim: to apply differences you have to be able to identify each row, and that demands a key — here, CREATE UNIQUE INDEX ux_mv_monthly_sales ON mv_monthly_sales (month);. Other details people discover late: you can create it empty with WITH NO DATA (and then querying it errors out until the first REFRESH); it accepts its own indexes, like a table; and it doesn't refresh itself —you have to call it from a cron, from the application's scheduler or from pg_cron.

When a materialized view pays off, specifically: the report takes seconds, it's queried many times a day, and nobody minds the data being an hour old. GreenStore's management dashboard is the perfect example; the cart's available stock, exactly the opposite.

In psql, \dv lists views, \dm lists materialized ones and \d+ v_sales_detail shows the columns and the definition. From SQL, SELECT viewname, viewowner FROM pg_views WHERE schemaname = 'public' and, above all, SELECT pg_get_viewdef('v_active_products'::regclass, TRUE): that second argument set to TRUE returns the definition formatted, and it's the right way to version views —you dump it, store it in the repository next to the code and review it like any other source file. The standard's views also exist (information_schema.views) and, for the dependency graph —"what breaks if I drop this view?"—, pg_depend.

Dialect note:

Engine	Views	OR REPLACE	Materialized
PostgreSQL	Yes, updatable if simple	Yes, only adding columns at the end	Yes, with manual REFRESH
MySQL 8	Yes, updatable, with WITH CHECK OPTION	Yes	They don't exist: emulated with a table and a scheduled event
SQLite	Yes, read-only	No (DROP + CREATE)	No
SQL Server	Yes	CREATE OR ALTER VIEW	Yes, called indexed views, and they maintain themselves (with plenty of restrictions)
Oracle	Yes	CREATE OR REPLACE VIEW, without the column restriction	Yes, with incremental (FAST REFRESH) and automatic refresh

• Believing a view speeds something up. It does exactly the same work. What stores data is the materialized view; a plain view only stores text.
• Trying to rename or remove a column with CREATE OR REPLACE VIEW. cannot change name of view column. You can only add columns at the end; for anything else, DROP + CREATE and recreate whatever depended on it.
• Nesting views on views on views. Once expanded, the planner sees a twenty-table query and stops reordering (join_collapse_limit). Two levels maximum.
• Putting ORDER BY in the definition. It isn't guaranteed to survive and it gets in the way: ordering is asked for by whoever queries.
• Writing through a view with a WHERE and no WITH CHECK OPTION. You can insert or update rows the view no longer shows, and they vanish before your eyes. And expecting an INSERT to work on a view with a JOIN or a GROUP BY: it isn't automatically updatable (cannot insert into view), you need an INSTEAD OF trigger (10-05).
• Forgetting the REFRESH. A materialized view with no refresh process is a frozen report that management reads as if it were today's. And with no UNIQUE index there's no CONCURRENTLY: every refresh will block reads.
• Using a view as a security mechanism without revoking the permission on the table. It protects nothing: whoever can read customers will keep reading the emails (11-03).
• Tip: name your views with a prefix (v_, mv_), and version their definitions with pg_get_viewdef(..., TRUE). A view created by hand in production that nobody has in the repository is invisible technical debt.
• Tip: one view per business metric. The real goal isn't writing less, it's making sure revenue is computed the same way everywhere.

Marketing wants to work with a v_customers_value view giving, for each of the 15 customers: id, full name, country, number of orders, units bought and revenue (0 if they've never bought).

1. Write it. Mind the type of JOIN and the COALESCE.
2. Query it ordered by revenue descending and check that customers 13, 14 and 15 come out with zeros and that the column's total is €727.95.
3. Is it automatically updatable? Justify your answer with the table in section 4.

On v_active_products defined with WITH CHECK OPTION, predict the result of each statement and then check it:

Every time it's opened, the management dashboard runs a query that takes 4 seconds: revenue by month and category since the business started. It's opened about 200 times a day and an hour's lag is acceptable.

1. View, materialized view or summary table? Justify with the comparison table.
2. Write the object you chose plus whatever is needed to refresh it without blocking anyone querying it.
3. What would change if the requirement were "the data must be as of the current second"?

Solution 1

1. The LEFT JOIN isn't negotiable: with INNER the three customers with no orders would disappear.

(First 4 of 15 rows.) Customers 13, 14 and 15 close with 0 and 0.00, and SELECT SUM(revenue) FROM v_customers_value gives 727.95. The COUNT(DISTINCT o.id) is compulsory: with COUNT(o.id) Lucía would have 9 "orders", one per line (04-04).

3. It is not automatically updatable, and it fails three requirements at once: it has three tables in the FROM, it has a GROUP BY and it has aggregates in the SELECT. An UPDATE on it would give cannot update view "v_customers_value", with the hint that an INSTEAD OF trigger is needed. And that makes sense: what should the database do if you tell it "set Sofia's revenue to €200"?

Solution 2

a) It works: UPDATE 1. The view is updatable —a single table, no aggregates—, row 5 is active before and after, and the change goes to products.

b) UPDATE 1, and then COUNT returns 18. The UPDATE runs against the table, so WITH CHECK OPTION doesn't come into play: it only polices writes made through the view. The tomato disappears from v_active_products with no further warning, which is precisely the desired behaviour for a soft delete.

c) It works: INSERT 0 1. The INSERT doesn't mention active, so the new row takes the table's DEFAULT TRUE, satisfies the view's WHERE and WITH CHECK OPTION lets it through. It would have failed if you'd written active as FALSE explicitly, or if the WHERE filtered on a column whose default value didn't satisfy it. Reload the script afterwards: you've just added a product 21 to the catalogue.

d) It fails, because v_sales_by_category depends on it:

DROP VIEW v_sales_detail CASCADE would work, and it would drop v_sales_by_category too without asking. That's the danger of section 5's nesting, now in the form of a dependency.

Solution 3

1. A materialized view. All three criteria match the middle column of the table: the read is expensive (4 s), it repeats a lot (200 times a day = 800 seconds of CPU daily) and lag is tolerated. A plain view would save nothing; a summary table maintained by triggers would give instant data, but at the cost of complexity and of a charge on every INSERT into order_lines that nobody here asked for.

2.

And in cron, hourly: REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_month_category;. Without that unique index, CONCURRENTLY fails and the refresh takes ACCESS EXCLUSIVE, leaving the dashboard unreachable for the 4 seconds the recomputation takes.

3. If the data must be as of the current second, the materialized view is out: a plain view, well indexed (module 8) and, if even then it doesn't get below 4 seconds, a summary table maintained by triggers (10-05), with all its complexity cost and its risk of drifting out of sync. The honest question, before any of that, is whether "as of the current second" is a real requirement or a habit: in a monthly report it almost never is.

The first tool in module 10 is also the cheapest:

• A view is a query with a name. It doesn't store data, it gets expanded into the query that uses it —to the point that its name doesn't appear in the EXPLAIN— and therefore it speeds nothing up.
• CREATE OR REPLACE VIEW only lets you add columns at the end: no renaming, no removing, no reordering, no type changes. Anything else is DROP + CREATE, dragging along whatever depended on it.
• The five reasons: simplify (v_sales_detail's four JOINs, 47 lines and €727.95), standardize a metric so revenue is computed the same way everywhere, encapsulate the soft delete (v_active_products, 19 of 20 products — 05-04's promise), decouple the application from the physical schema as a stable contract against 05-06's expand/contract, and expose only part of the data (with the permissions in 11-03).
• A view is automatically updatable if it comes from a single table, with no aggregates, no DISTINCT, no GROUP BY and with simple columns; WITH CHECK OPTION prevents writing rows the view itself wouldn't show. For everything else, an INSTEAD OF trigger (10-05).
• Nesting views is convenient and dangerous: past two levels, the planner stops reordering the JOINs and the plan degrades. When in doubt, EXPLAIN the whole query (08-05).
• Materialized views do store rows: 12 precomputed months read instantly that go stale until the REFRESH. CONCURRENTLY avoids blocking reads, but it demands a UNIQUE index. They pay off in expensive reports that tolerate hour-old data.

A view solves the problem of reusing a query across sessions and across people. But often what you want isn't to reuse it, but to understand it: to break a forty-line query into named steps, here and now, without creating any permanent object. In the next lesson, common table expressions (CTEs), you'll put WITH in front of the SELECT and see how 07-04's three-level nested derived tables turn into a readable list of steps; you'll chain several CTEs where each one builds on the previous; you'll understand why what the old tutorials say about materialization stopped being true in PostgreSQL 12; and, above all, you'll write your first recursive query to finally walk the full employee hierarchy and the referral chain that 03-06 left half-done.