âš¡IntermediateViews & Indexes
Views (CREATE VIEW)
Create reusable named queries as views to simplify complex SQL.
Overview
A view is a named query stored in the database. Querying a view is like querying a table the view definition is substituted at query time. Views simplify complex queries, enforce consistent column selection and filtering, and can provide a security layer (grant SELECT on a view, not the underlying table).
PostgreSQL supports updatable views (simple views on a single table can be INSERT/UPDATE/DELETEd), and WITH CHECK OPTION prevents inserts/updates that would make the row invisible through the view.
Key Points
- •CREATE VIEW name AS SELECT ... defines a reusable query
- •Querying a view expands to its definition at runtime (no storage)
- •UPDATE/DELETE/INSERT work on simple single-table views
- •WITH CHECK OPTION enforces that new rows satisfy the view's WHERE
- •DROP VIEW name; removes the view definition
- •CREATE OR REPLACE VIEW update definition without dropping dependents
Examples
Create and use a view
-- Create a view for active users
CREATE VIEW active_users AS
SELECT id, email, created_at
FROM users
WHERE is_active = TRUE;
-- Query it like a table
SELECT * FROM active_users WHERE created_at > NOW() - INTERVAL '30 days';
-- Update the view definition
CREATE OR REPLACE VIEW active_users AS
SELECT id, email, username, created_at
FROM users WHERE is_active = TRUE;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4