Module 24 — Views

1. CREATE VIEW

What it is: CREATE VIEW saves a SELECT query as a named virtual table. The view doesn't store data — it runs the underlying query each time you access it. You can query a view just like a regular table.

Why we use it: Views hide complexity — instead of writing a 10-line JOIN query every time, you write it once as a view and query it with SELECT * FROM my_view. They also provide a stable API that doesn't break when underlying tables change.

When we use it: When the same complex query is used in multiple places, when building APIs that need a simplified data interface, or when providing access to specific data without exposing the full table.

-- Create a view for published posts with author info
CREATE VIEW published_posts AS
SELECT
    posts.title,
    posts.published_at,
    authors.name AS author_name
FROM posts
JOIN authors ON posts.author_id = authors.id
WHERE posts.published = true;
 
-- Use the view like a table
SELECT * FROM published_posts;
SELECT * FROM published_posts WHERE author_name = 'Alice';

2. Why Use Views?

What it is: Views serve four main purposes: simplifying complex queries, enforcing access control, ensuring consistency, and building reusable reporting queries.

Why we use it: Without views, every developer writes the same complex JOIN query independently — leading to inconsistency and bugs. Views centralize the logic and provide a single source of truth.

When we use it: In every project with complex queries, multi-developer teams, reporting requirements, or access control needs.

3. Modifying Views

What it is: Views can be replaced (updated) or dropped. CREATE OR REPLACE VIEW updates the view's definition without dropping it. DROP VIEW removes the view entirely.

Why we use it: As requirements change, the view's query may need updating — adding columns, changing filters, or modifying joins. REPLACE lets you update without breaking dependent code.

When we use it: When the underlying query needs to change, when adding new columns to the view, or when removing a view that's no longer needed.

Replace a view

CREATE OR REPLACE VIEW published_posts AS
SELECT
    posts.title,
    posts.published_at,
    authors.name AS author_name,
    categories.name AS category
FROM posts
JOIN authors ON posts.author_id = authors.id
JOIN categories ON posts.category_id =

Drop a view

DROP VIEW IF EXISTS published_posts;

4. Updatable Views

What it is: Simple views (one table, no aggregates, no GROUP BY, no DISTINCT) are automatically updatable — you can INSERT, UPDATE, and DELETE through them. The changes are applied to the underlying table.

Why we use it: Updatable views let you restrict what users can see while still allowing them to modify data. For example, a view showing only active users lets you update active users without seeing inactive ones.

When we use it: When building restricted interfaces — users can modify their own data but not see others, or when providing a simplified interface for data entry.

-- Simple view (updatable)
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE is_active = true;
 
-- This works:
UPDATE active_users SET email = 'new@example.com' WHERE id = 1;
 
-- This also works:
INSERT INTO active_users (username, email) VALUES

5. Views vs Materialized Views

What it is: A materialized view stores the query result physically on disk, unlike a regular view which re-runs the query each time. Materialized views are pre-computed and can be indexed for fast reads, but they become stale until refreshed.

Why we use it: When the underlying query is expensive (complex joins, aggregations) and the data doesn't need to be real-time. Materialized views provide fast reads at the cost of stale data.

When we use it: In dashboards, reports, analytics, or any read-heavy scenario where the data can be a few minutes/hours old.

-- Materialized view (cached)
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at);
 
-- Refresh to update the cached data

Common Mistakes to Avoid

1. Expecting views to be fast — Views re-run the underlying query each time
2. Trying to index a view — Only materialized views support indexes
3. Forgetting to refresh materialized views — They become stale

Practice Exercise

1. Create a view called course_summary that shows course title, instructor, and price
2. Query the view with a WHERE clause
3. Create a materialized view for course statistics (count per instructor, avg price)
4. Refresh the materialized view after adding a new course
5. Drop both views