PostgreSQL — Views

Basic View

CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true;

Query View

SELECT * FROM active_users;

View with Join

CREATE VIEW user_orders AS
SELECT 
    u.name,
    COUNT(o.id) as order_count,
    SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

Modify View

CREATE OR REPLACE VIEW active_users AS
SELECT * FROM users WHERE active = true AND verified = true;

Drop View

DROP VIEW active_users;
DROP VIEW IF EXISTS active_users;

Materialized View

CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id;

-- Refresh
REFRESH MATERIALIZED VIEW user_stats;

View vs Table

Feature	View	Table
Storage	No	Yes
Updates	Limited	Yes
Performance	Query runs each time	Pre-stored
Complexity	Simple queries	Any data

Mini Practice

1. Create a view
2. Query a view
3. Modify a view
4. Create materialized view

Up Next

Continue with Indexes — database indexes.