Views and materialized views

Save a query under a name so it can be queried like a table.

Syntax

CREATE [OR REPLACE] VIEW name AS SELECT ...;
CREATE MATERIALIZED VIEW name AS SELECT ... [WITH [NO] DATA];
REFRESH MATERIALIZED VIEW [CONCURRENTLY] name;
DROP VIEW [IF EXISTS] name;

A view stores the query, not the data: every time you select from it, the query runs against the current tables. Use views to share a standard definition ("revenue", "active customer") and hide complexity.
CREATE OR REPLACE VIEW can add columns at the end but cannot rename, remove or retype existing ones (drop and recreate instead).
Simple views (one table, no aggregates/DISTINCT/GROUP BY) are automatically updatable.
A materialized view stores the result. It is fast to read but stale until REFRESH MATERIALIZED VIEW. CONCURRENTLY lets readers continue during the refresh but needs a unique index on the view.
Views can be indexed only if materialized.

Examples

A reusable definition (modifies schema) shop

CREATE VIEW order_values AS
SELECT o.order_id, o.customer_id, o.ordered_at, o.status,
       sum(oi.quantity * oi.unit_price - oi.discount_amount) + o.shipping_fee AS order_value
FROM orders o
JOIN order_items oi USING (order_id)
GROUP BY o.order_id;

SELECT status, count(*) AS orders, round(avg(order_value), 2) AS avg_value
FROM order_values
GROUP BY status
ORDER BY orders DESC;

Anyone can now query order_values without re-deriving the order value formula.

Materialized view (modifies schema) starter

CREATE MATERIALIZED VIEW monthly_units AS
SELECT date_trunc('month', order_date)::date AS month, sum(quantity) AS units
FROM orders
GROUP BY 1;

INSERT INTO orders (order_id, customer_id, product_id, order_date, quantity, status)
VALUES (500, 1, 1, '2025-01-15', 100, 'processing');

SELECT 'stored in view' AS source, units FROM monthly_units WHERE month = '2025-01-01'
UNION ALL
SELECT 'live from orders', sum(quantity) FROM orders WHERE order_date >= '2025-01-01' AND order_date < '2025-02-01';

REFRESH MATERIALIZED VIEW monthly_units;
SELECT units AS after_refresh FROM monthly_units WHERE month = '2025-01-01';

The stored result ignores the new order until REFRESH MATERIALIZED VIEW runs.

Pitfalls

Expecting a materialized view to update itself.
Changing column names with CREATE OR REPLACE VIEW (not allowed).
Stacking many views on views, which hides expensive queries.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: Indexed views instead of materialized views (strict requirements).
MySQL: No materialized views.
Oracle: Materialized views can refresh on commit or on a schedule.
Snowflake: Materialized views are maintained automatically (Enterprise edition).