Views

Overview

A view is a named, stored query that behaves like a virtual table—it holds no data itself but returns fresh results each time you query it. Views simplify complex joins, encapsulate business logic, and can restrict which columns users see. A materialized view additionally caches the result set on disk and must be refreshed.

Syntax / Usage

Create a view with CREATE VIEW and query it like any table. Use CREATE OR REPLACE to update its definition, and DROP VIEW to remove it.

-- Standard (virtual) view
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE active = true;

SELECT * FROM active_users WHERE name LIKE 'A%';

-- Materialized view (cached, must refresh)
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', created_at) AS month, SUM(total) AS revenue
FROM orders
GROUP BY 1;

REFRESH MATERIALIZED VIEW monthly_revenue;

Examples

Encapsulate a multi-table join so reports query one simple object:

CREATE VIEW order_summary AS
SELECT o.id, u.name AS customer, o.total, o.created_at
FROM orders o
JOIN users u ON u.id = o.user_id;

SELECT customer, SUM(total) AS lifetime_value
FROM order_summary
GROUP BY customer;

Expose only safe columns to limit access to sensitive data:

CREATE VIEW public_profiles AS
SELECT id, display_name, avatar_url
FROM users;  -- hides email, password_hash, etc.

Common Mistakes

• Expecting a plain view to improve performance—it re-runs the query every time
• Forgetting to REFRESH a materialized view, so it returns stale data
• Building deeply nested views that hide expensive joins and hurt performance
• Assuming all views are updatable; complex views often reject INSERT/UPDATE
• Dropping a table without noticing dependent views that then break

See Also

select joins sql-query-optimization