0
0
PostgreSQLquery~10 mins

Updatable views in PostgreSQL - Step-by-Step Execution

Choose your learning style9 modes available
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
StepActionEvaluationResult
1Create view emp_viewView defined as SELECT id, name, salary FROM employeesView emp_view created
2Update emp_view set salary = salary + 100 where id=2Check if emp_view is updatableYes, emp_view is updatable
3Apply update to base table employeesFind row with id=2Salary updated by +100 in employees table
4Confirm updateSelect from employees where id=2Row shows updated salary
5Attempt update on non-updatable viewCheck view definition complexityReject update with error
💡 Update stops when view is not updatable or after base table is updated
Variable Tracker
VariableStartAfter Step 3Final
salary (id=2)500051005100
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
Test your understanding
Look at the execution_table, at which step is the update applied to the base table?
AStep 2
BStep 4
CStep 3
DStep 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?
A5100
B5000
C4900
DNo 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?
AUpdate succeeds and changes base tables
BUpdate is rejected with error
CUpdate partially applies
DView 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.