PostgreSQL — Topic 5: Views

5.1

Standard Views

Create, query and manage regular views

▾

Create View

A standard view is created with CREATE VIEW. The query inside is never executed at creation time — only when someone queries the view.

How a view works

SELECT * FROM v_active_users

→

PostgreSQL expands the view

→

Runs underlying SQL on real tables

📌 No data is stored. Fresh data every time you query.

Basic syntax:

            
-- Create a view
CREATE VIEW v_active_users AS
SELECT
    user_id,
    name,
    email,
    created_at
FROM users
WHERE is_deleted = FALSE
  AND is_active = TRUE;

-- Query the view exactly like a table
SELECT * FROM v_active_users;

-- You can also filter/join on top of a view
SELECT * FROM v_active_users
WHERE email LIKE '%@gmail.com';
          

✅

Real-world use: Your backend API calls SELECT * FROM v_active_users. The view hides the is_deleted and is_active complexity from the application code.

A more complex view joining multiple tables — a very common pattern:

            
-- E-commerce: view showing orders with customer info
CREATE VIEW v_order_summary AS
SELECT
    o.order_id,
    o.created_at      AS order_date,
    o.status,
    u.name            AS customer_name,
    u.email           AS customer_email,
    COUNT(oi.item_id)  AS item_count,
    SUM(oi.price * oi.quantity) AS total_amount
FROM orders o
JOIN users u ON u.user_id = o.user_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY
    o.order_id, o.created_at, o.status,
    u.name, u.email;

-- Now a simple query replaces a complex join
SELECT * FROM v_order_summary
WHERE status = 'pending'
ORDER BY order_date DESC;
          

Replace & Drop Views

Use CREATE OR REPLACE VIEW to update a view without dropping it first. Use DROP VIEW to remove it.

            
-- Update view definition (column order must be the same or additive)
CREATE OR REPLACE VIEW v_active_users AS
SELECT
    user_id,
    name,
    email,
    phone,          -- ← new column added
    created_at
FROM users
WHERE is_deleted = FALSE
  AND is_active = TRUE;

-- Drop if it exists
DROP VIEW IF EXISTS v_active_users;

-- List all views in your database
SELECT table_name
FROM information_schema.views
WHERE table_schema = 'public';
          

Why use Standard Views?

Simplicity

Hide complex JOINs and filters behind a simple name. Devs just SELECT * FROM v_orders.

Security

Expose only certain columns or rows. Hide salary, password hash, internal flags.

Reuse

Write the JOIN once in a view; every query reuses it. No copy-paste SQL everywhere.

Abstraction

Rename or restructure tables underneath; the view stays the same for the API layer.

5.2

Updatable Views

When can you INSERT / UPDATE / DELETE through a view?

▾

What Makes a View Updatable?

PostgreSQL can automatically allow INSERT, UPDATE, and DELETE on a view — but only if the view meets strict conditions:

Rule	Must Be True?	Why
Single base table (no JOINs)	Yes	PG must know which table to update
No GROUP BY / HAVING	Yes	Aggregated rows can't map back to single rows
No DISTINCT	Yes	One view row must = one table row
No aggregate functions (SUM, COUNT…)	Yes	Can't write back to computed values
No window functions	Yes	Same reason — computed, not stored
No UNION / INTERSECT / EXCEPT	Yes	Ambiguous which table to touch

A simple view that qualifies:

            
-- This view is automatically updatable ✅
CREATE VIEW v_active_users AS
SELECT user_id, name, email
FROM users
WHERE is_active = TRUE;

-- You can now UPDATE through the view
UPDATE v_active_users
SET name = 'Alice Smith'
WHERE user_id = 42;
-- ↑ This actually runs: UPDATE users SET name = 'Alice Smith' WHERE user_id = 42

-- You can also INSERT through the view
INSERT INTO v_active_users (name, email)
VALUES ('Bob', 'bob@example.com');
-- Note: is_active will be NULL unless you set a DEFAULT on the column
          

WITH CHECK OPTION

By default, you can UPDATE a row through a view so it no longer matches the view's WHERE clause — the row disappears from the view. WITH CHECK OPTION prevents that.

❌ Without CHECK OPTION

                UPDATE v_active_users

                SET is_active = FALSE

                WHERE user_id = 42;

                -- Row vanishes from view silently!

✅ With CHECK OPTION

                UPDATE v_active_users

                SET is_active = FALSE

                WHERE user_id = 42;

                -- ERROR: violates WITH CHECK OPTION

            
-- WITH CHECK OPTION guards the view's WHERE clause
CREATE VIEW v_active_users AS
SELECT user_id, name, email, is_active
FROM users
WHERE is_active = TRUE
WITH CHECK OPTION;

-- Trying to set is_active = FALSE will raise an error
UPDATE v_active_users
SET is_active = FALSE
WHERE user_id = 42;
-- ERROR: new row violates check option for view "v_active_users"

-- LOCAL vs CASCADED for nested views
CREATE VIEW v_gmail_active AS
SELECT * FROM v_active_users
WHERE email LIKE '%@gmail.com'
WITH LOCAL CHECK OPTION;  -- only checks THIS view's WHERE
-- CASCADED (default) checks parent views too
          

🧠 Quick check — which view is automatically updatable?

INSTEAD OF Triggers (for complex views)

When your view is too complex to be automatically updatable (e.g. it has a JOIN), you can add an INSTEAD OF trigger to handle writes yourself.

            
-- Complex view with a JOIN (not auto-updatable)
CREATE VIEW v_user_orders AS
SELECT
    u.user_id, u.name, u.email,
    o.order_id, o.status, o.created_at
FROM users u
JOIN orders o ON u.user_id = o.user_id;

-- Write a function to handle updates manually
CREATE OR REPLACE FUNCTION fn_update_user_orders()
RETURNS TRIGGER AS $$
BEGIN
    -- Update only the users table
    UPDATE users
    SET name = NEW.name, email = NEW.email
    WHERE user_id = NEW.user_id;

    -- Update only the orders table
    UPDATE orders
    SET status = NEW.status
    WHERE order_id = NEW.order_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach the INSTEAD OF trigger to the view
CREATE TRIGGER tg_update_user_orders
INSTEAD OF UPDATE ON v_user_orders
FOR EACH ROW EXECUTE FUNCTION fn_update_user_orders();

-- Now you can UPDATE the joined view!
UPDATE v_user_orders
SET name = 'Alice', status = 'shipped'
WHERE user_id = 1 AND order_id = 101;
          

5.3

Materialized Views

Cached query results — the key to fast reporting

▾

Standard View vs Materialized View

Standard View — runs every time

Query view

→

Expand SQL

→

Read from base tables

→

Results

Materialized View — pre-computed, stored on disk

Base tables change

→

REFRESH materializes it

→

Data stored on disk

Query mat view

→

Reads from disk (instant!)

⚡ Materialized views are fast to read but may show stale data until refreshed.

Feature	Standard View	Materialized View
Stores data?	No (virtual)	Yes (on disk)
Speed	Same as base query	Very fast (pre-computed)
Data freshness	Always fresh	Stale until refreshed
Indexable?	No	Yes!
Updatable?	Sometimes	No
Best for	Always-current data, simple filtering	Dashboards, reports, expensive aggregations

Creating a Materialized View

Use CREATE MATERIALIZED VIEW. The query runs immediately and results are stored.

            
-- Expensive daily revenue report — perfect for materialization
CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT
    DATE(o.created_at)             AS sale_date,
    p.category,
    COUNT(o.order_id)             AS order_count,
    SUM(oi.price * oi.quantity)   AS revenue,
    AVG(oi.price * oi.quantity)   AS avg_order_value
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p   ON p.product_id = oi.product_id
WHERE o.status = 'completed'
GROUP BY DATE(o.created_at), p.category
ORDER BY sale_date DESC;

-- Create an index on the materialized view for fast lookup
CREATE INDEX idx_mv_daily_revenue_date
    ON mv_daily_revenue (sale_date);

-- Query is instant now!
SELECT * FROM mv_daily_revenue
WHERE sale_date = CURRENT_DATE - 1;

-- Skip population at creation time (populate later)
CREATE MATERIALIZED VIEW mv_daily_revenue AS
    ...
WITH NO DATA;  -- ← empty until first REFRESH
          

Refresh

Materialized views don't auto-update. You must call REFRESH MATERIALIZED VIEW to rebuild them from the latest data.

            
-- Basic refresh — locks the view while rebuilding
REFRESH MATERIALIZED VIEW mv_daily_revenue;

-- During refresh above, queries on mv_daily_revenue BLOCK
-- This is bad for production if queries hit it constantly

⚠️

Problem: A plain REFRESH locks the materialized view. No one can query it while it's being rebuilt — potentially for minutes on large datasets!

Concurrent Refresh

REFRESH MATERIALIZED VIEW CONCURRENTLY rebuilds in the background. Readers can still query the old data while the new data is being computed.

            
-- Requirement: the mat view must have a UNIQUE index
CREATE UNIQUE INDEX idx_mv_revenue_unique
    ON mv_daily_revenue (sale_date, category);

-- Now you can refresh concurrently
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;
-- ↑ Queries continue to work during this operation!

-- Drop a mat view
DROP MATERIALIZED VIEW IF EXISTS mv_daily_revenue;
          

🎮 Interactive: See how refresh strategies compare

← Click a button to see the behavior

Reporting Patterns

In production systems, materialized views are used with a scheduler (pg_cron, a cron job, or Celery) to refresh on a schedule. Here are the most common patterns:

📊

Daily Dashboard

Refresh every night at 2 AM when traffic is low. Reports always show yesterday's data.

⚡

Near-Realtime

CONCURRENT REFRESH every 5 minutes. Users see slightly stale data but queries are instant.

🏗️

Aggregation Layer

Chain views: raw → cleaned → aggregated → mat view. Each level builds on the previous.

🤖

Event-triggered

Trigger a refresh after large batch inserts or imports via application logic or a trigger.

A real scheduled refresh using pg_cron:

            
-- Install pg_cron extension
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule concurrent refresh every 10 minutes
SELECT cron.schedule(
    'refresh_revenue',
    '*/10 * * * *',   -- every 10 minutes
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue$$
);

-- Check scheduled jobs
SELECT * FROM cron.job;

-- Remove a job
SELECT cron.unschedule('refresh_revenue');
          

5.4

Real-World Example: AI SaaS App

All three view types used together in one system

▾

Scenario — Multi-tenant AI Chat SaaS

You're building a ChatGPT-style product. You have organizations, users, conversations, and messages tables. Here's how views tie it all together.

            
-- ─── Base tables ──────────────────────────────────────
CREATE TABLE organizations (
    org_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name       TEXT NOT NULL,
    plan       TEXT NOT NULL DEFAULT 'free'
);

CREATE TABLE users (
    user_id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id     UUID REFERENCES organizations(org_id),
    email      TEXT UNIQUE NOT NULL,
    name       TEXT NOT NULL,
    is_active  BOOLEAN DEFAULT TRUE
);

CREATE TABLE conversations (
    conv_id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    UUID REFERENCES users(user_id),
    title      TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE messages (
    msg_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conv_id    UUID REFERENCES conversations(conv_id),
    role       TEXT NOT NULL,  -- 'user' or 'assistant'
    content    TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    tokens     INTEGER DEFAULT 0
);

-- ─── 1. Standard View: active users ────────────────────
CREATE VIEW v_active_users AS
SELECT
    u.user_id, u.name, u.email,
    o.name AS org_name, o.plan
FROM users u
JOIN organizations o ON o.org_id = u.org_id
WHERE u.is_active = TRUE;

-- ─── 2. Standard View: conversation history ─────────────
CREATE VIEW v_conversation_detail AS
SELECT
    c.conv_id, c.title,
    c.created_at                        AS started_at,
    u.name                              AS user_name,
    COUNT(m.msg_id)                     AS message_count,
    SUM(m.tokens)                      AS total_tokens,
    MAX(m.created_at)                   AS last_message_at
FROM conversations c
JOIN users u  ON u.user_id   = c.user_id
JOIN messages m ON m.conv_id = c.conv_id
GROUP BY c.conv_id, c.title, c.created_at, u.name;

-- ─── 3. Materialized View: per-org token usage ──────────
CREATE MATERIALIZED VIEW mv_org_usage AS
SELECT
    o.org_id,
    o.name                          AS org_name,
    o.plan,
    COUNT(DISTINCT u.user_id)      AS user_count,
    COUNT(DISTINCT c.conv_id)      AS total_conversations,
    SUM(m.tokens)                  AS total_tokens_used,
    DATE_TRUNC('month', m.created_at) AS month
FROM organizations o
JOIN users u         ON u.org_id   = o.org_id
JOIN conversations c ON c.user_id  = u.user_id
JOIN messages m      ON m.conv_id  = c.conv_id
GROUP BY o.org_id, o.name, o.plan,
         DATE_TRUNC('month', m.created_at);

-- Index for fast billing lookups
CREATE UNIQUE INDEX idx_mv_org_usage
    ON mv_org_usage (org_id, month);

-- Refresh nightly for billing
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_org_usage;

-- ─── Billing API query (instant!) ───────────────────────
SELECT org_name, plan, total_tokens_used
FROM mv_org_usage
WHERE org_id = 'acme-corp-uuid'
  AND month = DATE_TRUNC('month', CURRENT_DATE);
          

🚀

Pattern summary: Use standard views for the API layer (always fresh, hides joins). Use materialized views for dashboards and billing (pre-computed, super fast, refreshed on schedule).

📋

Quick Reference Cheat Sheet

All the commands at a glance

▾

            
── STANDARD VIEWS ──────────────────────────────────────────
CREATE VIEW view_name AS SELECT ...;
CREATE OR REPLACE VIEW view_name AS SELECT ...;
DROP VIEW IF EXISTS view_name;

── UPDATABLE VIEWS ─────────────────────────────────────────
-- single table, no GROUP BY, no DISTINCT, no UNION
CREATE VIEW v_name AS SELECT ... WITH CHECK OPTION;

── MATERIALIZED VIEWS ───────────────────────────────────────
CREATE MATERIALIZED VIEW mv_name AS SELECT ...;
CREATE MATERIALIZED VIEW mv_name AS SELECT ... WITH NO DATA;
REFRESH MATERIALIZED VIEW mv_name;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_name;
DROP MATERIALIZED VIEW IF EXISTS mv_name;

── INDEXING A MATERIALIZED VIEW ─────────────────────────────
CREATE INDEX idx_name ON mv_name (column);
CREATE UNIQUE INDEX idx_name ON mv_name (col1, col2);
-- unique index is required for CONCURRENT refresh

── INSPECT VIEWS ────────────────────────────────────────────
SELECT table_name FROM information_schema.views
  WHERE table_schema = 'public';

SELECT matviewname, definition
FROM pg_matviews
WHERE schemaname = 'public';

\dv   -- list views in psql
\dm   -- list materialized views in psql
          

Type	Stores Data	Refresh Needed	Indexable	Use When
Standard View	No	Never	No	API layer, always-fresh data
Updatable View	No	Never	No	Simplify writes + hide columns
Materialized View	Yes	Yes (manual/cron)	Yes	Dashboards, reports, billing