Views & Materialized Views

If you write the same join of orders, order items and products in ten places, a change in the business rule means ten fixes. A view stores a query under a name, so everyone uses one definition and reads it like a table. A materialized view goes further: it stores the query's result, so a heavy report can be read in milliseconds.

The SQL course introduces views. In this lesson you will create and change views in PostgreSQL, update data through them, restrict what they show, and use materialized views with REFRESH, including refreshing without blocking readers.

Creating a view

A view is a saved SELECT. It stores no data of its own; every time you query it, PostgreSQL runs its query against the current tables:

CREATE VIEW order_summary AS
SELECT o.order_id,
       o.order_date,
       o.status,
       c.first_name || ' ' || c.last_name AS customer,
       c.city,
       sum(oi.quantity * oi.unit_price) AS order_total,
       sum(oi.quantity) AS units
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY o.order_id, c.customer_id;

SELECT order_id, customer, city, order_total
FROM order_summary
WHERE status = 'delivered' AND order_total > 5000
ORDER BY order_total DESC;

Query OK
order_id | customer     | city     | order_total
---------+--------------+----------+------------
110      | Sneha Gupta  | Delhi    | 32797.00
102      | Priya Verma  | Jaipur   | 15999.00
104      | Aarav Sharma | Rohtak   | 8999.00
112      | Isha Kapoor  | Gurugram | 8695.00
103      | Rohan Mehta  | Mumbai   | 5398.00
(5 rows)

The outer query adds its own WHERE and ORDER BY, just as with a table. The planner merges the view's query with yours, so a simple view costs nothing extra. Because it always reads current data, a new order appears in the view immediately.

Change a view with CREATE OR REPLACE VIEW (you may add columns at the end, but not remove or retype existing ones) and remove it with DROP VIEW. A table that a view depends on cannot be dropped without CASCADE, which drops the view too.

Updatable views

A simple view, one that selects from a single table without grouping, DISTINCT or set operations, is automatically updatable: INSERT, UPDATE and DELETE on the view change the underlying table. WITH CHECK OPTION stops changes that would make a row disappear from the view:

CREATE VIEW haryana_customers AS
SELECT customer_id, first_name, last_name, city, state, email, joined_on
FROM customers
WHERE state = 'Haryana'
WITH CHECK OPTION;

UPDATE haryana_customers SET city = 'Panipat' WHERE customer_id = 7;
SELECT customer_id, first_name, city FROM haryana_customers ORDER BY customer_id;

UPDATE haryana_customers SET state = 'Punjab' WHERE customer_id = 7;

Query OK
Query OK, 1 row affected
customer_id | first_name | city
------------+------------+---------
1           | Aarav      | Rohtak
7           | Vikram     | Panipat
10          | Isha       | Gurugram
(3 rows)

ERROR:  new row violates check option for view "haryana_customers"

Moving Vikram to another Haryana city is fine. Moving him to Punjab would take the row outside the view, so WITH CHECK OPTION rejects it. Views like this let you give a regional team access to only their customers. For full row-level control, see Roles, Privileges & Row-Level Security.

Materialized views

A materialized view runs its query once and stores the result like a table. Reading it is fast, but the data is a snapshot: it does not change until you refresh it.

CREATE MATERIALIZED VIEW category_sales AS
SELECT p.category,
       count(DISTINCT oi.order_id) AS orders,
       sum(oi.quantity) AS units,
       sum(oi.quantity * oi.unit_price) AS sales
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
GROUP BY p.category;

SELECT * FROM category_sales ORDER BY sales DESC;

INSERT INTO order_items VALUES (23, 115, 3, 1, 15999.00);
SELECT category, sales FROM category_sales WHERE category = 'Displays';

REFRESH MATERIALIZED VIEW category_sales;
SELECT category, sales FROM category_sales WHERE category = 'Displays';

Query OK
category    | orders | units | sales
------------+--------+-------+---------
Displays    | 2      | 3     | 47997.00
Audio       | 4      | 4     | 22996.00
Accessories | 8      | 13    | 21987.00
Storage     | 3      | 7     | 15743.00
Books       | 2      | 5     | 2250.00
(5 rows)

Query OK, 1 row affected
category | sales
---------+---------
Displays | 47997.00
(1 row)

Query OK
category | sales
---------+---------
Displays | 63996.00
(1 row)

After the new order line, the materialized view still shows the old Displays total until REFRESH MATERIALIZED VIEW runs the query again. This trade-off is ideal for dashboards over big tables, where data that is a few minutes or an hour old is fine: schedule a refresh (for example with cron or the pg_cron extension) and every page load reads the stored result. You can also create indexes on a materialized view.

Refreshing without blocking readers

A plain REFRESH locks the materialized view, so dashboards wait while it rebuilds. REFRESH MATERIALIZED VIEW CONCURRENTLY builds the new result on the side and then applies only the differences, so readers keep working. It needs a unique index on the view:

CREATE UNIQUE INDEX category_sales_category_idx ON category_sales (category);
REFRESH MATERIALIZED VIEW CONCURRENTLY category_sales;

A concurrent refresh is slower than a plain one, but nobody notices it running.

View or materialized view?

Common mistakes

• Expecting a materialized view to update itself. It changes only on REFRESH.
• Using CONCURRENTLY without a unique index. The refresh fails; create one first.
• Stacking many views on views. Deep layers become hard to understand and tune. Keep them shallow.
• Using SELECT * in a view. The column list is fixed when the view is created, so new table columns do not appear. List columns explicitly.

Key points

• A view is a named, saved query; it stores no data and always shows current rows.
• Simple single-table views are automatically updatable; WITH CHECK OPTION keeps changes inside the view.
• A materialized view stores the query result; read it fast, refresh it with REFRESH MATERIALIZED VIEW.
• REFRESH ... CONCURRENTLY avoids blocking readers but needs a unique index.
• Views help with reuse and access control; materialized views help with heavy reports.

CREATE VIEW product_sales AS
SELECT p.product_id, p.product_name, p.category,
       coalesce(sum(oi.quantity), 0) AS units,
       coalesce(sum(oi.quantity * oi.unit_price), 0) AS sales
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.product_id
GROUP BY p.product_id;

SELECT product_name, units, sales
FROM product_sales
ORDER BY sales DESC
LIMIT 3;