PostgreSQL - Views and Materialized Views
Consider a view defined as CREATE VIEW dept_view AS SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;. What is the result of executing UPDATE dept_view SET count = 10 WHERE dept_id = 2;
Consider a view defined as CREATE VIEW dept_view AS SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;. What is the result of executing UPDATE dept_view SET count = 10 WHERE dept_id = 2;
medium📝 query result Q5 of 15
Step-by-Step Solution
Solution:
Step 1: Analyze the view definition
The view uses GROUP BY and aggregate COUNT, making it non-updatable by default.Step 2: Predict update behavior
PostgreSQL will raise an error when trying to update a non-updatable view without triggers.Final Answer:
PostgreSQL raises an error because the view is not updatable. -> Option AQuick Check:
Aggregate views are not updatable [OK]
Quick Trick: Views with GROUP BY are not updatable [OK]
Common Mistakes:
- Assuming aggregate views can be updated
- Thinking update affects base tables
- Believing updates silently fail
Master "Views and Materialized Views" in PostgreSQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore PostgreSQL Quizzes