Updatable views in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is an updatable view in PostgreSQL?

An updatable view is a virtual table that allows you to insert, update, or delete rows through the view, and these changes affect the underlying base tables.

intermediate

Which conditions must a view meet to be automatically updatable in PostgreSQL?

The view must be based on a single table, include all NOT NULL columns without defaults, and not contain GROUP BY, DISTINCT, aggregates, or set operations.

advanced

How can you make a complex view updatable in PostgreSQL?

By defining INSTEAD OF triggers on the view that specify how to handle insert, update, and delete operations on the underlying tables.

beginner

What happens if you try to update a non-updatable view without triggers in PostgreSQL?

PostgreSQL will return an error because it cannot determine how to propagate changes to the underlying tables.

intermediate

Explain the role of INSTEAD OF triggers in updatable views.

INSTEAD OF triggers intercept insert, update, or delete commands on a view and execute custom code to modify the underlying tables accordingly.

Which of the following views is automatically updatable in PostgreSQL?

A A view with DISTINCT keyword

B A view with GROUP BY clause

C A view joining multiple tables

D A view based on a single table without aggregates

Only views based on a single table without aggregates or grouping are automatically updatable.

What is required to update a view that joins multiple tables in PostgreSQL?

A INSTEAD OF triggers must be defined

B Nothing, it updates automatically

C Use a materialized view instead

D Add a primary key to the view

Views joining multiple tables need INSTEAD OF triggers to handle updates.

If a view does not include all NOT NULL columns without defaults, what happens when you try to insert through it?

A Insert succeeds with NULLs

B Insert fails with an error

C Insert ignores missing columns

D Insert updates only existing columns

PostgreSQL requires all NOT NULL columns without defaults to be present for inserts through views.

Which SQL command can you NOT use in a view if you want it to be automatically updatable?

A SELECT

B JOIN

C GROUP BY

D WHERE

GROUP BY prevents automatic updatability of views.

What does an INSTEAD OF trigger do on an updatable view?

A Executes custom code instead of the default action

B Prevents any changes to the view

C Automatically updates all underlying tables

D Deletes the view after update

INSTEAD OF triggers run custom code to handle modifications on views.

Describe what makes a view updatable in PostgreSQL and how you can update views that are not automatically updatable.

Think about the rules for automatic updatability and how triggers help.

Explain the purpose and function of INSTEAD OF triggers in the context of updatable views.

Consider how PostgreSQL handles changes on views that can't update automatically.

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