Updatable views in PostgreSQL

Introduction

Updatable views let you change data through a saved query, making it easier to work with complex data without touching the original tables directly.

You want to simplify data access for users by showing only certain columns or rows.

You need to allow users to update data safely without giving them full access to all tables.

You want to hide complex joins but still allow updates on the combined data.

You want to enforce some rules or filters on data updates automatically.

You want to present data in a specific way but keep it editable.

Syntax

PostgreSQL

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- To make the view updatable, it must follow certain rules.
-- You can also use INSTEAD OF triggers for complex views.

Not all views are updatable. Simple views on one table usually are.

For complex views, you can write INSTEAD OF triggers to handle updates.

Examples

This view shows only active employees. You can update the 'name' or 'active' fields through this view.

PostgreSQL

CREATE VIEW simple_view AS
SELECT id, name FROM employees WHERE active = true;

This view limits data to the Sales department. Updates affect only those employees.

PostgreSQL

CREATE VIEW limited_view AS
SELECT id, salary FROM employees WHERE department = 'Sales';

This view joins two tables. It is not automatically updatable without triggers.

PostgreSQL

CREATE VIEW complex_view AS
SELECT e.id, e.name, d.name AS dept_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

Sample Program

This example creates a table and a view showing only active employees. It updates a name through the view and shows that inactive employees cannot be updated via the view.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  active BOOLEAN,
  department TEXT
);

INSERT INTO employees (name, active, department) VALUES
('Alice', true, 'Sales'),
('Bob', false, 'HR'),
('Carol', true, 'Sales');

CREATE VIEW active_employees AS
SELECT id, name, department FROM employees WHERE active = true;

-- Update name of an active employee through the view
UPDATE active_employees SET name = 'Alicia' WHERE id = 1;

-- Try to update an inactive employee (won't affect anything)
UPDATE active_employees SET name = 'Robert' WHERE id = 2;

-- Check the employees table
SELECT * FROM employees ORDER BY id;

Important Notes

Views that select from only one table without complex joins or aggregates are usually updatable.

If your view is complex, use INSTEAD OF triggers to define how inserts, updates, and deletes work.

Updating through views helps keep your data safe and your queries simple.

Summary

Updatable views let you change data through a saved query.

Simple views on one table are usually updatable automatically.

For complex views, use triggers to handle updates.