PostgreSQL Views and Materialized Views (REFRESH)

PostgreSQL views and materialized views

A view is a saved query with a name. Querying it runs the underlying query each time, so it’s always current. Views hide complexity, give everyone the same definition of a metric (“revenue”), and can expose only some columns for security. Simple single-table views are even updatable.

A materialized view stores the query’s result like a table. Reading it is fast, but the data is a snapshot until you run REFRESH MATERIALIZED VIEW. Use it for expensive reports that don’t need to be live to the second. REFRESH ... CONCURRENTLY (which needs a unique index) lets reads continue during the refresh.

Examples

Edit the code and run it. Output is printed the way psql prints it.

22.1Create and use a view

CREATE OR REPLACE VIEW order_summary AS
SELECT o.id AS order_id,
       o.order_date,
       o.status,
       c.name AS customer,
       c.country,
       sum(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.name, c.country;

SELECT country, count(*) AS orders, sum(total) AS revenue
FROM order_summary
WHERE status <> 'cancelled'
GROUP BY country
ORDER BY revenue DESC;

The join and grouping are written once inside the view. Anyone can now query order_summary as if it were a simple table.

22.2Views are always live

BEGIN;
UPDATE order_items SET quantity = 100 WHERE order_id = 1 AND product_id = 1;
SELECT order_id, total FROM order_summary WHERE order_id = 1;
ROLLBACK;
SELECT order_id, total FROM order_summary WHERE order_id = 1;

The view reflects the change immediately, and the original value again after the rollback, because a view stores no data of its own.

22.3Materialized view snapshot

DROP MATERIALIZED VIEW IF EXISTS monthly_sales;
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', order_date)::date AS month, sum(total) AS revenue
FROM order_summary
WHERE status <> 'cancelled'
GROUP BY 1;

SELECT * FROM monthly_sales WHERE month = '2024-01-01';

BEGIN;
UPDATE order_items SET quantity = 100 WHERE order_id = 1 AND product_id = 1;
SELECT 'stale' AS state, * FROM monthly_sales WHERE month = '2024-01-01';
REFRESH MATERIALIZED VIEW monthly_sales;
SELECT 'refreshed' AS state, * FROM monthly_sales WHERE month = '2024-01-01';
ROLLBACK;

The materialized view keeps the old number until it is refreshed. (The ROLLBACK undoes both the update and the refresh.) This example depends on the order_summary view, so run the first example above first.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 22.1

Create a view active_employees with id, first_name, last_name and salary of active employees only.

Hint

CREATE VIEW ... AS SELECT ... WHERE is_active

Solution

CREATE VIEW active_employees AS SELECT id, first_name, last_name, salary FROM employees WHERE is_active;