CREATE VIEW
intermediateddl
2 min read
What is a VIEW?
A VIEW is a named query stored in the database. It acts as a virtual table — when you query a view, PostgreSQL executes the underlying query and returns the results.
Syntax
CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;Replace an existing view:
CREATE OR REPLACE VIEW view_name AS
SELECT ...;When to Use
- Simplifying complex queries that are used frequently
- Providing a clean interface to users without exposing table complexity
- Enforcing row-level security by filtering sensitive data
- Abstracting schema changes from application queries
Key Points
- Virtual Table — Views do not store data. They execute the query each time.
- Updatable — Simple views (single table, no aggregates) can support INSERT/UPDATE/DELETE.
- CREATE OR REPLACE — Updates the definition without dropping dependent objects.
- Materialized Views — For caching results, use CREATE MATERIALIZED VIEW (stores data on disk).
- Permissions — You can grant SELECT on a view without granting access to underlying tables.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
CREATE VIEW Challenge
Write a query that solve this task: a view showing students with their enrollment count.
Expected result
A view named student_summary is created. Query it with SELECT * FROM student_summary;
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is a VIEW?
Initializing database...Each run starts from fresh sample data.
More Examples
Query a view
Use the view like a regular table.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Do views store data?
No. Regular views are virtual — they execute the query each time. Materialized views store data and need manual refreshing.
Can I INSERT into a view?
Only simple views (single table, no aggregates, no DISTINCT/GROUP BY) are automatically updatable. Complex views require INSTEAD OF triggers.