Updatable views in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of UPDATE on a simple updatable view

Consider a table employees(id, name, salary) and a view high_earners defined as SELECT * FROM employees WHERE salary > 50000. What will be the result of this query?

UPDATE high_earners SET salary = salary + 5000 WHERE id = 3 RETURNING *;

PostgreSQL

CREATE TABLE employees (id INT PRIMARY KEY, name TEXT, salary INT);
INSERT INTO employees VALUES (1, 'Alice', 60000), (2, 'Bob', 45000), (3, 'Carol', 70000);
CREATE VIEW high_earners AS SELECT * FROM employees WHERE salary > 50000;

A Returns the updated row for id=3 with salary increased to 75000

B Returns no rows because id=3 is not in the view

C Raises an error because views cannot be updated

D Updates all rows in employees, not just id=3

💡 Hint

Think about whether the view filters rows and if the updated row still meets the filter.

The view high_earners filters employees with salary > 50000. The row with id=3 has salary 70000, so it is included. Updating salary to 75000 keeps it in the view. The query returns the updated row.

🧠 Conceptual

intermediate

Which views are updatable in PostgreSQL?

Which of the following views is not updatable by default in PostgreSQL?

A A view selecting all columns from a single table with no WHERE clause

B A view selecting columns with a WHERE filter from one table

C A view selecting all columns from a single table without aggregation

D A view that uses a JOIN between two tables

💡 Hint

Think about what makes a view simple enough to be updatable.

Views based on a single table without aggregation or joins are updatable. Views with joins are not updatable by default because PostgreSQL cannot determine how to update multiple tables.

📝 Syntax

advanced

Which statement creates an updatable view with an INSTEAD OF trigger?

Given a view v_emp_dept joining employees and departments, which option correctly creates an INSTEAD OF trigger to allow updates on the view?

A CREATE TRIGGER trg_update INSTEAD OF UPDATE ON v_emp_dept FOR EACH ROW EXECUTE FUNCTION update_emp_dept();

B CREATE TRIGGER trg_update AFTER UPDATE ON v_emp_dept FOR EACH ROW EXECUTE PROCEDURE update_emp_dept();

C CREATE TRIGGER trg_update INSTEAD OF UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION update_emp_dept();

D CREATE TRIGGER trg_update BEFORE UPDATE ON v_emp_dept FOR EACH ROW EXECUTE FUNCTION update_emp_dept();

💡 Hint

INSTEAD OF triggers must be created on views, not tables, and use EXECUTE FUNCTION syntax in PostgreSQL 12+.

INSTEAD OF triggers on views intercept update commands and allow custom logic. The trigger must be created on the view, use INSTEAD OF timing, and EXECUTE FUNCTION syntax.

❓ optimization

advanced

Improving performance of updates on an updatable view

You have an updatable view based on a large table with a WHERE clause. Updates on the view are slow. Which approach can improve update performance?

A Create a materialized view instead of a regular view

B Rewrite the view to use a JOIN with another large table

C Add an index on the columns used in the WHERE clause of the view

D Remove the WHERE clause from the view to include all rows

💡 Hint

Think about how indexes help queries and updates.

Adding an index on columns used in the WHERE clause helps PostgreSQL quickly find rows to update, improving performance. Joining with another large table or removing filters can slow down updates. Materialized views are not updatable.

🔧 Debug

expert

Why does this UPDATE on a view raise an error?

Given the view definition:

CREATE VIEW v_emp AS SELECT id, name, salary, salary * 1.1 AS adjusted_salary FROM employees;

Why does this query raise an error?

UPDATE v_emp SET adjusted_salary = 80000 WHERE id = 2;

A Because the employees table has no rows with id = 2

B Because adjusted_salary is a computed column and cannot be updated

C Because the UPDATE statement syntax is incorrect

D Because the view does not include the primary key

💡 Hint

Consider which columns in a view can be updated.

Columns in a view that are computed expressions (like salary * 1.1) cannot be updated because they do not directly map to a column in the underlying table.