Views & Materialized Views

A view is a stored query that behaves like a table. PostgreSQL gives you two flavours: regular views (re-run each access, always fresh) and materialized views (results stored on disk, refreshed on demand). Each fits different needs.

Regular Views

A view is a virtual table. Its definition is stored, but its data is not — every SELECT against it runs the underlying query.

Creating a View

CREATE VIEW high_earners AS
SELECT id, name, dept, salary
FROM employees
WHERE salary > 80000;

Now query it like a table:

SELECT * FROM high_earners;
SELECT dept, COUNT(*) FROM high_earners GROUP BY dept;

Why use views?

• Abstraction — hide complex joins or business logic behind a simple name.
• Security — grant access to a view that exposes only certain columns / rows, while the base table stays restricted.
• Reuse — define a calculation once and reference it from many queries.
• Stable interface — refactor the underlying tables without breaking client queries.

Replacing or Dropping

CREATE OR REPLACE VIEW high_earners AS
SELECT id, name, dept, salary, hire_date
FROM employees
WHERE salary > 75000;

DROP VIEW high_earners;
DROP VIEW IF EXISTS high_earners;

Updatable Views

PostgreSQL automatically makes a view updatable (allowing INSERT/UPDATE/DELETE) when it is simple enough:

• Selects from exactly one table or another simple updatable view.
• No DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, set-returning functions, window functions, or aggregates in the top-level SELECT.
• All output columns are simple references to columns of the underlying table.

CREATE VIEW it_employees AS
SELECT id, name, salary, hire_date
FROM employees
WHERE dept = 'IT';

-- Works automatically
UPDATE it_employees SET salary = salary * 1.05 WHERE id = 1;
INSERT INTO it_employees (id, name, salary, hire_date)
VALUES (99, 'Zoe', 80000, CURRENT_DATE);
DELETE FROM it_employees WHERE id = 99;

WITH CHECK OPTION

A user could insert a row through it_employees that doesn't satisfy dept = 'IT' — because the view filters but doesn't enforce. WITH CHECK OPTION blocks any modification that would make a row "invisible" through the view:

CREATE VIEW it_employees AS
SELECT id, name, dept, salary, hire_date
FROM employees
WHERE dept = 'IT'
WITH CHECK OPTION;

-- Now this fails:
INSERT INTO it_employees VALUES (99, 'Zoe', 'Sales', 80000, CURRENT_DATE);
-- ERROR: new row violates check option for view "it_employees"

INSTEAD OF Triggers — Make Any View Writable

For complex views (joins, aggregates), define an INSTEAD OF trigger to translate writes back to the base tables:

CREATE VIEW employee_summary AS
SELECT e.id, e.name, e.salary, d.name AS dept_name
FROM employees e JOIN departments d ON e.dept_id = d.id;

CREATE OR REPLACE FUNCTION employee_summary_update()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE employees
    SET name = NEW.name, salary = NEW.salary
    WHERE id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_employee_summary_update
INSTEAD OF UPDATE ON employee_summary
FOR EACH ROW EXECUTE FUNCTION employee_summary_update();

Materialized Views

A materialized view stores the query result physically. Reads are fast (no recomputation), but the data is a snapshot — you must refresh it to pick up changes.

Creating

CREATE MATERIALIZED VIEW dept_stats AS
SELECT dept,
       COUNT(*) AS headcount,
       AVG(salary)::numeric(10,2) AS avg_salary,
       MAX(salary) AS max_salary
FROM employees
GROUP BY dept
WITH DATA;

• WITH DATA (default) — populate immediately.
• WITH NO DATA — create the structure but skip the initial query; you must refresh before it's queryable.

SELECT * FROM dept_stats;

Refreshing

The data does NOT update automatically when the underlying tables change. You refresh it manually:

-- Locks the view (no reads during refresh)
REFRESH MATERIALIZED VIEW dept_stats;

-- Non-blocking refresh (PG 9.4+)
REFRESH MATERIALIZED VIEW CONCURRENTLY dept_stats;

Indexing Materialized Views

Because the data is stored, you can index it just like a table:

CREATE UNIQUE INDEX dept_stats_pk ON dept_stats (dept);
CREATE INDEX dept_stats_avg ON dept_stats (avg_salary);

This makes lookups even faster.

Drop

DROP MATERIALIZED VIEW dept_stats;

Refresh Strategies

Since refresh is manual, you choose when:

-- Example: refresh nightly with pg_cron
SELECT cron.schedule('nightly-stats',
    '0 2 * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY dept_stats');

View vs Materialized View — When to Use Which

Recursive Views

For recursive logic (org charts, graph traversal), PostgreSQL supports CREATE RECURSIVE VIEW as syntactic sugar for a recursive CTE:

CREATE RECURSIVE VIEW org_chart (id, name, manager_id, level) AS
    SELECT id, name, manager_id, 1
    FROM employees WHERE manager_id IS NULL
  UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e JOIN org_chart oc ON e.manager_id = oc.id;

SELECT * FROM org_chart ORDER BY level, name;

Inspecting Views

-- List all views
\dv

-- View definition (psql)
\d+ high_earners

-- From SQL
SELECT pg_get_viewdef('high_earners', true);

-- All materialized views
SELECT schemaname, matviewname, ispopulated
FROM pg_matviews;

Common Pitfalls

Summary

• A view is a stored query — always fresh, no storage cost, recomputed each access.
• A materialized view stores the result on disk — fast reads, manual refresh, indexable.
• Simple views are automatically updatable; complex ones need INSTEAD OF triggers.
• Use WITH CHECK OPTION to keep inserts/updates inside the view's filter.
• For materialized views, add a unique index and use REFRESH … CONCURRENTLY to avoid locking readers.
• Choose regular views for abstraction; materialized views for performance on read-heavy workloads where slight staleness is acceptable.