Apply Rules to Enable Updates in Complex Views

Last update on October 29 2025 12:36:50 (UTC/GMT +8 hours)

Update Data Through a View Using a Rule

Write a PostgreSQL query to update a customer’s email via a view that uses a rule for update redirection.

Solution:

-- Update the customer's contact email through the CustomerView.
UPDATE CustomerView  -- The view is defined with rules to update the Customers table.
SET contact_email = '[email protected]'  -- Set the new email address.
WHERE customer_id = 200;  -- Identify the customer.

Explanation:

Purpose of the Query:

The goal is to update underlying table data via a view that is not inherently updatable by using a rule.
This demonstrates how PostgreSQL rules can be created to handle INSERT, UPDATE, or DELETE operations on non-updatable views.

Key Components:

UPDATE CustomerView : Specifies the view that is made update-capable through a rule.
SET contact_email = '[email protected]' : Modifies the email field.
WHERE customer_id = 200 : Filters the target row.

Real-World Application:

Useful when complex views need to support DML operations without exposing direct table access.

Notes:

The rule must be defined beforehand so that the UPDATE is redirected appropriately.
Without such a rule, PostgreSQL would not permit the update on the view.

For more Practice: Solve these Related Problems:

Write a PostgreSQL query to update an employee’s position via a view that employs a rule to redirect the update to the underlying Employees table.
Write a PostgreSQL query to update a customer’s phone number through a view that uses a rule to modify the underlying Customers table.
Write a PostgreSQL query to update the discount rate via a view that leverages a rule to update the underlying Discounts table.
Write a PostgreSQL query to update a supplier’s contact details through a view that uses a rule for redirecting the update to the Suppliers table.

Go to:

PREV : Attempt to Update a Non-Updatable Aggregated View.
NEXT : Update Data Through a View with a Subquery Condition.

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