0PricingLogin▼
SQL Academy · Lesson

Updatable Views and INSTEAD OF Triggers

Make views updatable directly, or use INSTEAD OF triggers when the view aggregates or joins data.

Automatically Updatable Views

PostgreSQL makes simple views updatable automatically. INSERT/UPDATE/DELETE on the view is rewritten as the same operation on the base table:

CREATE VIEW active_users AS
  SELECT id, email, full_name FROM users WHERE deleted_at IS NULL;

UPDATE active_users SET full_name = 'Alice' WHERE id = 1;
-- Translates to UPDATE on the users table.

Requirements for Auto-Updatable

The view must:

  • Reference exactly ONE base table
  • Have no aggregates, GROUP BY, HAVING, DISTINCT
  • Have no LIMIT, OFFSET
  • Have no set operations (UNION etc.)
  • Map columns directly (no expressions in the SELECT list — or only some)

All lessons in this course

  1. Plain Views: Logical Reuse
  2. Updatable Views and INSTEAD OF Triggers
  3. Materialized Views and REFRESH Strategies
  4. When to Pre-Aggregate
← Back to SQL Academy