Views and materialised views

Part of the free LogicWiz SQL & Databases course, Module 15: Views, functions and security. The queries and practice run in the app on real Postgres.

Meher pastes the same 12-line "delivered orders with totals" query into five reports. Then the definition of delivered changes, and she has to fix it five times. A view lets her write it once.

You'll learn to

• Save a query as a view and query it like a table
• Know when a view is fresh and when a materialised view is stale
• Refresh a materialised view and protect an updatable view with WITH CHECK OPTION

Learn

A view is a saved query

A view is a SELECT with a name. You create it once, then use the name wherever a table could go.

• A view stores no rows. Postgres stores only the query text.
• Every time you read the view, Postgres runs the query again, so the result is always fresh.
• People who use the view don't need to know the joins inside it.

Think of a view as a window onto the tables, not a copy of them.

Create a view, then use it like a table

CREATE VIEW delivered_orders AS
SELECT o.order_id, c.name AS customer, r.name AS restaurant, o.ordered_at
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN restaurants r ON r.restaurant_id = o.restaurant_id
WHERE o.status = 'delivered';

SELECT restaurant, COUNT(*) AS orders
FROM delivered_orders
GROUP BY restaurant
ORDER BY orders DESC, restaurant;

The second query has no joins and no status filter: the view hides them. You can filter, group and join a view like any table.

A view is always up to date

CREATE VIEW premium_customers AS
SELECT customer_id, name, city
FROM customers
WHERE is_premium;

SELECT COUNT(*) AS before FROM premium_customers;

UPDATE customers SET is_premium = true WHERE customer_id = 2;

SELECT COUNT(*) AS after FROM premium_customers;

Meher became premium and the view saw it at once, because the view's query ran again on the second read.

Materialised view: a stored copy

Sometimes the query behind a view is slow: a big join plus GROUP BY over millions of orders. Running it on every page load is wasteful.

A materialised view runs the query once and stores the result rows on disk, like a table.

• Reading it is fast: no joins, just stored rows.
• But it does not change when the tables change. It is a snapshot.
• REFRESH MATERIALIZED VIEW name; runs the query again and replaces the rows.

Use it for dashboards that can be a few minutes old, like a daily sales report.

Stale until you refresh

CREATE MATERIALIZED VIEW city_customers AS
SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city;

INSERT INTO customers VALUES (13, 'Neha Rao', 'Pune', '2025-07-01', false, NULL);

SELECT customers FROM city_customers WHERE city = 'Pune';

REFRESH MATERIALIZED VIEW city_customers;

SELECT customers FROM city_customers WHERE city = 'Pune';

The first read still says 1 Pune customer: the snapshot was taken before Neha joined. After REFRESH it says 2.

View vs materialised view

Writing through a view

A simple view (one table, no GROUP BY, DISTINCT or aggregates) is updatable: you can INSERT, UPDATE and DELETE through it and Postgres changes the base table.

There is a catch. A view WHERE city = 'Mumbai' would happily accept a Pune customer, who then vanishes from the view. Add WITH CHECK OPTION and Postgres rejects any row the view itself could not show.

Views are also a security tool: show the support team name and city, hide the phone number, and grant them only the view (lesson 4).

A materialised view does not refresh itself. If a report built on one shows yesterday's numbers, nobody ran REFRESH MATERIALIZED VIEW. Schedule the refresh (a cron job, or after the nightly load), or use a plain view when the data must be live.

Exams ask: a view is a virtual table; it gives logical data independence (the tables can change while the view keeps its shape) and security (users see only what the view shows). Interviews ask the view vs materialised view difference and "do views make queries faster?" (a plain view: no, it is just the query).

In one line

A view stores a query and is always fresh; a materialised view stores rows and is stale until REFRESH MATERIALIZED VIEW.

Interview corner

What is the difference between a view and a materialised view?

A view stores only its query and runs it on every read, so it is always current but only as fast as the query. A materialised view stores the result rows, so reads are fast and can be indexed, but the data is stale until REFRESH MATERIALIZED VIEW. Use views to simplify and secure, materialised views for slow reports that may lag.

Does a view make a query faster?

A plain view, no: Postgres expands the view into the outer query and plans the whole thing, so it costs the same as writing the query by hand. Only a materialised view (or an index on the base tables) changes the speed.

Can you INSERT or UPDATE through a view?

Yes, if it is a simple view: one table, no GROUP BY, DISTINCT, aggregates, set operations or LIMIT. Postgres turns the change into a change on the base table. Add WITH CHECK OPTION so rows the view's WHERE would hide are rejected. Complex views need an INSTEAD OF trigger.