Views

By now you have written the order-total calculation four times. The join of orders to order_items, the SUM(quantity * unit_price), the GROUP BY with four columns. Every copy is a place for a typo, and every report that needs it has to remember the cancelled-order rule.

A view stores the query under a name. Anyone who selects from the view runs the saved query.

Creating a view#

CREATE VIEW order_totals AS
SELECT o.order_id,
       o.customer_id,
       o.order_date,
       o.status,
       SUM(oi.quantity)                    AS units,
       SUM(oi.quantity * oi.unit_price)    AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.customer_id, o.order_date, o.status;

Now it reads like a table:

SELECT order_id, customer_id, total
FROM order_totals
WHERE status = 'delivered'
ORDER BY total DESC
LIMIT 3;

 order_id | customer_id | total
----------+-------------+--------
        9 |           7 | 607.50
        2 |           1 | 334.00
        3 |           2 | 199.00

A view stores no data#

This is the thing to hold onto. A regular view is a stored SELECT, nothing more. Creating one copies no rows. When you query it, PostgreSQL substitutes its definition into your query, then plans the combined statement as a whole.

  SELECT ... FROM order_totals WHERE status = 'delivered'
                      |
                      v   (rewrite: replace view with its definition)
  SELECT ... FROM (orders JOIN order_items ... GROUP BY ...)
            WHERE status = 'delivered'
                      |
                      v
                 planner and executor

Two things follow. The data is always current, since there is nothing to go stale. And a view is not faster than the query inside it. It makes the query reusable, not cheaper.

What views are good for#

One definition of a business rule. "Revenue" excludes cancelled orders. Put that in a view and every dashboard agrees.

Hiding complexity. Analysts select from order_totals and never see the join.

Limiting access. Grant SELECT on a view that exposes some columns, and withhold the base table.

CREATE VIEW customer_public AS
SELECT customer_id, full_name, city, country
FROM customers;                  -- no email, no phone

GRANT SELECT ON customer_public TO analyst;

By default a view runs with its owner's permissions, which is what makes this work. PostgreSQL 15 added WITH (security_invoker = true) to make it use the caller's permissions instead, which is the safer choice when row-level security is involved.

A stable interface. You can reorganize the underlying tables and keep the view's output the same, so reports keep working.

Updatable views#

A simple view, one table with no aggregates, DISTINCT or set operations, accepts INSERT, UPDATE and DELETE, and PostgreSQL writes through to the base table.

CREATE VIEW us_customers AS
SELECT * FROM customers WHERE country = 'USA'
WITH CHECK OPTION;

WITH CHECK OPTION is the interesting part. It rejects any change that would make a row fall outside the view's WHERE.

UPDATE us_customers SET country = 'Canada' WHERE customer_id = 2;
-- ERROR:  new row violates check option for view "us_customers"

Without the option, that update would succeed and the row would vanish from the view. The view rows would silently shrink. A view like order_totals, with a GROUP BY, is not updatable, and PostgreSQL says so. For those you can add INSTEAD OF triggers, covered in Module 6.

Changing a view#

CREATE OR REPLACE VIEW swaps the definition, with limits. You may add new columns at the end. You may not rename, reorder, remove or change the type of existing columns. For those, drop and recreate, which fails if other views depend on it unless you use CASCADE.

One more trap: SELECT * inside a view is expanded when the view is created. Add a column to the table afterward and the view does not gain it. Name your columns explicitly.

Materialized views#

Sometimes the saved query is expensive, and the people reading it do not need to-the-second freshness. A materialized view stores the result on disk like a table.

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', o.order_date)::date AS month,
       SUM(oi.quantity * oi.unit_price)        AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY 1;

Reads are now instant, since they scan a small stored table. The price is staleness. The contents stay as they were until you refresh:

REFRESH MATERIALIZED VIEW monthly_revenue;

A plain refresh locks the view against reads while it rebuilds. For a view people read constantly, add a unique index and refresh without blocking:

CREATE UNIQUE INDEX ON monthly_revenue (month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

PostgreSQL does not refresh materialized views on its own. Schedule it with cron, pg_cron or your job runner.

Stacking views#

A view can select from another view. It composes nicely until it does not. Three or four layers deep, nobody remembers what the bottom does, the planner has a very large query to optimize, and a change at the base breaks things at the top. Keep the stack shallow.

Common mistakes#

Believing a view makes a query faster. It only names it. Slow underneath means slow through the view.

Expecting a materialized view to update itself. Stale numbers on a dashboard are usually a missed refresh.

SELECT * in a view definition. Later columns do not appear, and CREATE OR REPLACE cannot easily fix it.

Writing through a view without WITH CHECK OPTION. Rows slip out of the view without an error.

Deep view stacks. Debugging becomes archaeology.

Practice#

1. Create order_totals and query it for non-cancelled orders over 300. You should get orders 2, 8, 9 and 12.
2. Create a view product_sales with product name, units sold and revenue for non-cancelled orders, including products never sold at zero.
3. Create us_customers with WITH CHECK OPTION. Try to update a customer's country through it and read the error. Then try the same update on the base table.
4. Create the materialized view monthly_revenue. Insert a new order and order line, query the view, and note it has not changed. Refresh and check again.
5. Create a restricted role, grant it access to customer_public only, connect as that role and confirm that selecting from customers is denied.