Updatable views in PostgreSQL - Mini Project: Build & Apply

Creating and Using Updatable Views in PostgreSQL

📖 Scenario: You work at a bookstore that keeps track of books and their prices in a database. The manager wants a simple way to see and update the prices without changing other book details.

🎯 Goal: Build an updatable view in PostgreSQL that shows book titles and prices. Then, update the price through the view.

📋 What You'll Learn

Create a table called books with columns id, title, and price

Insert three specific books into the books table

Create a view called book_prices that shows only title and price

Update the price of a book using the book_prices view

💡 Why This Matters

🌍 Real World

Updatable views let users safely see and change parts of data without exposing the full table, useful in many business applications.

💼 Career

Database developers and administrators often create updatable views to simplify user access and maintain data integrity.

1

Create the books table and insert data

Create a table called books with columns id (integer primary key), title (text), and price (numeric). Then insert these three books exactly: (1, 'The Great Gatsby', 10.99), (2, '1984', 8.99), and (3, 'To Kill a Mockingbird', 12.50).

PostgreSQL

-- Create the books table and insert the three books
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Create the book_prices view

Create a view called book_prices that selects only the title and price columns from the books table.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
(1, 'The Great Gatsby', 10.99),
(2, '1984', 8.99),
(3, 'To Kill a Mockingbird', 12.50);

-- Create the book_prices view selecting title and price
-- Your code here

Hint

Use CREATE VIEW view_name AS SELECT ... to create the view.

3

Update a book price using the view

Write an UPDATE statement to change the price of '1984' to 9.99 using the book_prices view.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
(1, 'The Great Gatsby', 10.99),
(2, '1984', 8.99),
(3, 'To Kill a Mockingbird', 12.50);

CREATE VIEW book_prices AS
SELECT title, price FROM books;

-- Update the price of '1984' to 9.99 using the book_prices view
-- Your code here

Hint

Use UPDATE view_name SET column = value WHERE condition to update through the view.

4

Verify the update by selecting from the base table

Write a SELECT statement to get all columns from the books table to confirm the price of '1984' has changed.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
(1, 'The Great Gatsby', 10.99),
(2, '1984', 8.99),
(3, 'To Kill a Mockingbird', 12.50);

CREATE VIEW book_prices AS
SELECT title, price FROM books;

UPDATE book_prices SET price = 9.99 WHERE title = '1984';

-- Select all columns from books to verify the update
-- Your code here

Hint

Use SELECT * FROM books to see all columns and confirm the price change.

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