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.