Updatable views in PostgreSQL - Step-by-Step Execution

Concept Flow - Updatable views

Create View

↓

Query View

↓

Check if View is Updatable

Yes No↓

Allow UPDATE/INSERT/DELETE

↓

Apply Changes to Base Tables

This flow shows how an updatable view allows changes to be made through it, which then affect the underlying base tables.

Execution Sample

PostgreSQL

CREATE VIEW emp_view AS
SELECT id, name, salary FROM employees;

UPDATE emp_view SET salary = salary + 100 WHERE id = 2;

This code creates a simple view and updates a row through the view, which updates the base table.

Execution Table

Step	Action	Evaluation	Result
1	Create view emp_view	View defined as SELECT id, name, salary FROM employees	View emp_view created
2	Update emp_view set salary = salary + 100 where id=2	Check if emp_view is updatable	Yes, emp_view is updatable
3	Apply update to base table employees	Find row with id=2	Salary updated by +100 in employees table
4	Confirm update	Select from employees where id=2	Row shows updated salary
5	Attempt update on non-updatable view	Check view definition complexity	Reject update with error

💡 Update stops when view is not updatable or after base table is updated

Variable Tracker

Variable	Start	After Step 3	Final
salary (id=2)	5000	5100	5100

Key Moments - 2 Insights

Why can't I update a view with joins or aggregates?

Because such views are not simple enough for PostgreSQL to map changes back to base tables, as shown in execution_table row 5 where update is rejected.

Does updating a view always change the base table?

Yes, if the view is updatable, the update affects the underlying base table directly, as shown in execution_table row 3.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the update applied to the base table?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Check the 'Action' and 'Result' columns in execution_table row 3

According to variable_tracker, what is the salary of id=2 after the update?

A 5100

B 5000

C 4900

D No change

💡 Hint

Look at the 'Final' column for salary (id=2) in variable_tracker

If the view includes a join, what happens when you try to update it?

A Update succeeds and changes base tables

B Update is rejected with error

C Update partially applies

D View automatically becomes updatable

💡 Hint

Refer to execution_table row 5 about non-updatable views

Concept Snapshot

Updatable views allow changes through the view to affect base tables.
Simple SELECT views without joins or aggregates are updatable.
Updates on complex views are rejected.
Use views to simplify data access but check updatability.
PostgreSQL enforces these rules automatically.

Full Transcript

Updatable views in PostgreSQL let you change data through a view, which then updates the underlying base tables. The process starts by creating a view with a simple SELECT statement. When you run an UPDATE on the view, PostgreSQL checks if the view is updatable. If yes, it applies the change to the base table. If the view is complex, like having joins or aggregates, updates are rejected. For example, updating salary through a simple view changes the base table's salary. This ensures data integrity and consistent updates.

Practice

1. What is an updatable view in PostgreSQL?

easy

A. A function that returns a set of rows

B. A view that only shows data but cannot be changed

C. A table that stores data permanently

D. A view that allows you to change data in the underlying table through it

Solution

1. Step 1: Understand the definition of a view

   A view is a saved query that shows data from one or more tables.

2. Step 2: Identify what makes a view updatable

   An updatable view lets you change data in the underlying table by modifying the view.

3. Final Answer:

   A view that allows you to change data in the underlying table through it -> Option D

4. Quick Check:

   Updatable view = change data through view [OK]

Hint: Updatable views let you edit data through the view [OK]

Common Mistakes:

• Confusing views with tables
• Thinking all views are read-only
• Believing views store data themselves

2. Which of the following is the correct syntax to create a simple updatable view on a single table employees showing id and name columns?

easy

A. CREATE VIEW emp_view AS SELECT id, name FROM employees;

B. CREATE VIEW emp_view AS UPDATE employees SET name = 'John';

C. CREATE TABLE emp_view AS SELECT id, name FROM employees;

D. CREATE FUNCTION emp_view() RETURNS TABLE(id INT, name TEXT) AS $$ SELECT id, name FROM employees; $$ LANGUAGE SQL;

Solution

1. Step 1: Recall the syntax to create a view

   The syntax is CREATE VIEW view_name AS SELECT columns FROM table;

2. Step 2: Check each option

   CREATE VIEW emp_view AS SELECT id, name FROM employees; matches the correct syntax for a simple view. The other options are incorrect because they use UPDATE, CREATE TABLE, or FUNCTION instead of CREATE VIEW.

3. Final Answer:

   CREATE VIEW emp_view AS SELECT id, name FROM employees; -> Option A

4. Quick Check:

   CREATE VIEW uses SELECT query [OK]

Hint: CREATE VIEW uses SELECT, not UPDATE or TABLE [OK]

Common Mistakes:

• Using UPDATE instead of SELECT in CREATE VIEW
• Confusing CREATE VIEW with CREATE TABLE
• Trying to create a function instead of a view

3. Given the table products(id, name, price) and the view CREATE VIEW cheap_products AS SELECT id, name FROM products WHERE price < 100; What happens if you try to update the name of a product through cheap_products view?

medium

A. The update changes only the view but not the underlying table

B. The update fails because the view filters rows with a WHERE clause

C. The update succeeds and changes the product name in the products table

D. The update causes a syntax error

Solution

1. Step 1: Understand updatable views with WHERE clause

   Views that select from a single base table with a restricting WHERE clause are updatable in PostgreSQL.

2. Step 2: Check the effect of updating through filtered view

   Updating the name of a product visible in the view (price < 100) will successfully update the underlying products table.

3. Final Answer:

   The update succeeds and changes the product name in the products table -> Option C

4. Quick Check:

   Single-table views with WHERE are updatable [OK]

Hint: Single-table views with WHERE filters are updatable [OK]

Common Mistakes:

• Thinking WHERE clause prevents updates
• Thinking update affects only the view
• Expecting syntax error instead of runtime error

4. You created a view with this SQL:
CREATE VIEW emp_dept AS SELECT e.id, e.name, d.name AS dept_name FROM employees e JOIN departments d ON e.dept_id = d.id;
When you try to update emp_dept to change an employee's name, you get an error. What is the main reason?

medium

A. The view joins multiple tables, so it is not automatically updatable

B. The view does not include the employee's ID column

C. The view uses aliases for columns, causing syntax errors

D. The view is missing a WHERE clause

Solution

1. Step 1: Analyze the view definition

   The view joins two tables: employees and departments.

2. Step 2: Understand updatable view rules for joins

   Views that join multiple tables are not automatically updatable in PostgreSQL because the system cannot decide how to update multiple tables from one view.

3. Final Answer:

   The view joins multiple tables, so it is not automatically updatable -> Option A

4. Quick Check:

   Joined views are not updatable by default [OK]

Hint: Views with joins are not updatable automatically [OK]

Common Mistakes:

• Thinking missing ID causes update failure
• Blaming column aliases for errors
• Assuming WHERE clause is required for updates

5. You want to make a complex view updatable that joins orders and customers tables. Which PostgreSQL feature should you use to allow updates through this view?

hard

A. Use a materialized view instead of a regular view

B. Create INSTEAD OF triggers on the view to handle updates

C. Add a WHERE clause to the view to filter rows

D. Create a function that returns the joined data

Solution

1. Step 1: Understand limitations of updatable views with joins

   Views joining multiple tables are not automatically updatable in PostgreSQL.

2. Step 2: Use INSTEAD OF triggers to enable updates

   INSTEAD OF triggers let you define custom update logic on complex views, making them updatable.

3. Final Answer:

   Create INSTEAD OF triggers on the view to handle updates -> Option B

4. Quick Check:

   INSTEAD OF triggers enable updates on complex views [OK]

Hint: Use INSTEAD OF triggers for complex view updates [OK]

Common Mistakes:

• Thinking WHERE clause makes view updatable
• Confusing materialized views with updatable views
• Believing functions replace update triggers