PostgreSQL Views

A view is a saved query you can select from as if it were a table.

CREATE VIEW book_details AS
SELECT
    b.id,
    b.title,
    b.price,
    a.name AS author_name,
    c.name AS category_name
FROM books b
LEFT JOIN authors a    ON a.id = b.author_id
LEFT JOIN categories c ON c.id = b.category_id;

SELECT * FROM book_details WHERE price < 500;

The view stores no data — it runs the underlying query every time.

Why views help

• Hide join complexity behind a simple name.
• Present a stable interface while the tables underneath change.
• Restrict access: grant SELECT on a view exposing only safe columns.

CREATE VIEW public_users AS
SELECT id, full_name, created_at FROM users WHERE is_active = true;

GRANT SELECT ON public_users TO reporting_role;

Replacing and dropping

CREATE OR REPLACE VIEW book_details AS SELECT ...;
DROP VIEW IF EXISTS book_details;

CREATE OR REPLACE cannot change existing column names or types — drop and recreate for that.

Updatable views

A simple view over one table, with no aggregates or DISTINCT, accepts INSERT/UPDATE/DELETE directly. Anything more complex needs an INSTEAD OF trigger.

CREATE VIEW cheap_books AS
SELECT * FROM books WHERE price < 300
WITH CHECK OPTION;

WITH CHECK OPTION stops you inserting a row through the view that the view itself would not show.

Materialised views

These do store the result — a cached snapshot for expensive queries:

CREATE MATERIALIZED VIEW sales_summary AS
SELECT
    date_trunc('month', created_at) AS month,
    COUNT(*) AS orders,
    SUM(total) AS revenue
FROM orders
GROUP BY 1;

CREATE UNIQUE INDEX ON sales_summary (month);

The data is frozen until you refresh it:

REFRESH MATERIALIZED VIEW sales_summary;

-- no read lock, but needs a unique index
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Use materialised views for dashboards and reports where a few minutes of staleness is acceptable and the query is too slow to run per page load.

View vs materialised view