Experience Update Limitations on an Aggregated View

Attempt to Update a Non-Updatable Aggregated View

Write a PostgreSQL query to update an aggregated view and note the expected error.

Solution:

-- Attempt to update an aggregated view (this operation will fail).
UPDATE DepartmentEmployeeCount  -- This view aggregates employee counts per department.
SET employee_count = employee_count + 1  -- Trying to modify an aggregate value.
WHERE department = 'Sales';

Explanation:

• Purpose of the Query:
• The goal is to demonstrate that views with aggregation (using GROUP BY) are not directly updatable.
• This highlights the limitations of updating views that summarize data.
• Key Components:
• UPDATE DepartmentEmployeeCount : Targets the aggregated view.
• SET employee_count = employee_count + 1 : Attempts to change an aggregate column.
• WHERE department = 'Sales' : Filters the specific group.
• Real-World Application:
• Serves as a reminder that certain view designs (e.g., summary reports) cannot support direct DML operations.

Notes:

• PostgreSQL will raise an error indicating that the view is not updatable.
• In such cases, you must update the underlying tables directly or use alternative approaches (e.g., INSTEAD OF triggers).

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to attempt updating an aggregated view that calculates the average salary per department, expecting an error.
• Write a PostgreSQL query to attempt modifying an aggregated view that summarizes monthly sales totals, and observe the update failure.
• Write a PostgreSQL query to attempt updating an aggregated view that groups data by product category, which should trigger an error message.
• Write a PostgreSQL query to attempt changing an aggregated view that computes total revenue per region, and capture the resulting error.

Go to:

• Practical Guide to Updating Views in PostgreSQL Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : Update Data through a Partitioned View.
NEXT : Update Data Through a View Using a Rule.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.