Views, updatable views and materialized views

A view is a query with a name. It is stored in the database and used like a table, but it holds no rows: every query that reads the view runs the stored query against the tables underneath, so the view always shows their current data. Views hide columns and rows that a user should not see, give a long join a short name, and keep old queries working after the tables are reorganised. Some views also accept INSERT, UPDATE and DELETE, which the database turns into changes of the underlying table: PostgreSQL does this by itself for simple views, and WITH CHECK OPTION refuses a change that would create a row the view cannot show. A materialized view works the other way round. It stores the result of its query as rows, so reading it is cheap, but it is only as fresh as its last REFRESH.

SQLite’s views are read-only and it has no materialized views, so each PostgreSQL behaviour below is shown as the output psql printed on PostgreSQL 18.6, starting from an empty database, and is followed by the SQLite workaround. The SQLite queries run on this page, on SQLite 3.49.1 compiled for the browser as sql.js 1.14.2.

A view is a stored query

CREATE TABLE students (
  roll  INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  dept  TEXT NOT NULL,
  cgpa  REAL,
  phone TEXT
);
INSERT INTO students VALUES
  (101, 'Asha',  'CSE', 8.9, '90000 00101'),
  (102, 'Ravi',  'ECE', 7.4, '90000 00102'),
  (103, 'Meera', 'CSE', 9.2, '90000 00103');

-- The view names a query. It shows no phone numbers and no other department.
CREATE VIEW cse_directory AS
  SELECT roll, name FROM students WHERE dept = 'CSE';

SELECT * FROM cse_directory ORDER BY roll;

-- A student added to the table appears through the view at once.
INSERT INTO students VALUES (104, 'Kabir', 'CSE', 6.8, '90000 00104');
SELECT * FROM cse_directory ORDER BY roll;

-- What SQLite stored for the view: the text of its definition, no rows.
SELECT type, name, sql FROM sqlite_schema WHERE name = 'cse_directory';

┌──────┬───────┐
│ roll │ name  │
├──────┼───────┤
│ 101  │ Asha  │
│ 103  │ Meera │
└──────┴───────┘
┌──────┬───────┐
│ roll │ name  │
├──────┼───────┤
│ 101  │ Asha  │
│ 103  │ Meera │
│ 104  │ Kabir │
└──────┴───────┘
┌──────┬───────────────┬──────────────────────────────────────────────────────┐
│ type │     name      │                         sql                          │
├──────┼───────────────┼──────────────────────────────────────────────────────┤
│ view │ cse_directory │ CREATE VIEW cse_directory AS                         │
│      │               │   SELECT roll, name FROM students WHERE dept = 'CSE' │
└──────┴───────────────┴──────────────────────────────────────────────────────┘

• The view shows two columns of the CSE rows only. Ravi’s row and every phone number stay out of it.
• Kabir, inserted into students after the view was created, appears through it straight away. Nothing was copied when the view was made, so there is nothing to keep in step.
• The schema table holds the view’s definition as text, and no data. SQLite parses that text again whenever the schema is loaded.

PostgreSQL stores a parsed query instead of text. Its views are built on the rule system: a view is a relation with no storage and an ON SELECT DO INSTEAD rule, conventionally named _RETURN. When a query names a view, the rewriter replaces the view by its stored query, and a view built on another view is replaced again, until only tables are left. The planner then pulls those subqueries up into one query and plans it as if you had written it out by hand:

# What PostgreSQL keeps for a view, and what the planner sees when a query uses one.
from pg_session import psql

psql("""
CREATE TABLE students (roll integer PRIMARY KEY, name text NOT NULL, dept text NOT NULL, cgpa numeric(3,1));
INSERT INTO students VALUES (101, 'Asha', 'CSE', 9.1);
CREATE VIEW cse AS SELECT * FROM students WHERE dept = 'CSE';
CREATE VIEW cse_toppers AS SELECT roll, name FROM cse WHERE cgpa >= 9;

-- Two views deep, but the plan reads the table once, with both conditions.
EXPLAIN (COSTS OFF) SELECT name FROM cse_toppers;

-- The stored definition: * was replaced by the columns the table had.
SELECT pg_get_viewdef('cse');
ALTER TABLE students ADD COLUMN hostel text DEFAULT 'Kaveri';
SELECT * FROM cse;

-- The views depend on these columns, so they cannot simply be dropped.
ALTER TABLE students DROP COLUMN cgpa;
""")

CREATE TABLE
INSERT 0 1
CREATE VIEW
CREATE VIEW
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on students
   Filter: ((cgpa >= '9'::numeric) AND (dept = 'CSE'::text))
(2 rows)

        pg_get_viewdef         
-------------------------------
  SELECT roll,                +
     name,                    +
     dept,                    +
     cgpa                     +
    FROM students             +
   WHERE (dept = 'CSE'::text);
(1 row)

ALTER TABLE
 roll | name | dept | cgpa 
------+------+------+------
  101 | Asha | CSE  |  9.1
(1 row)

ERROR:  cannot drop column cgpa of table students because other objects depend on it
DETAIL:  view cse depends on column cgpa of table students
view cse_toppers depends on view cse
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

The plan for cse_toppers is a single scan of students with the conditions of both views joined by AND; the views themselves cost nothing at run time. The session also shows a difference between the two engines. PostgreSQL replaced * by the four columns students had when the view was created, so the column hostel, added later, does not appear through cse, and the dependency stops anyone dropping a column that a view uses. SQLite keeps the text and reads it again:

CREATE TABLE students (roll INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO students VALUES (101, 'Asha');
CREATE VIEW everyone AS SELECT * FROM students;

-- Add a column after the view was created.
ALTER TABLE students ADD COLUMN hostel TEXT DEFAULT 'Kaveri';

-- SQLite reads the view's text again, so * now includes the new column.
SELECT * FROM everyone;

┌──────┬──────┬────────┐
│ roll │ name │ hostel │
├──────┼──────┼────────┤
│ 101  │ Asha │ Kaveri │
└──────┴──────┴────────┘

Name the columns in a view’s SELECT list rather than writing *. The view then means the same thing in every engine, and a new column never appears in a view by surprise.

What views are for

Restricting access. A user can be allowed to read a view without being allowed to read its table. By default PostgreSQL checks the table against the privileges of the view’s owner, so the view decides which columns and rows are visible. (The view option security_invoker checks the table against the user of the view instead.) The access control lesson shows such a grant at work.

Logical data independence. Applications are written against the names and columns they query. If those come from a view, the tables underneath can change shape while the view keeps the old one. PostgreSQL’s tutorial recommends using views freely for this reason: they put a stable interface in front of tables that change as an application grows. Here hostel rooms move out of students into their own table, and an old report keeps working:

-- First design: hostel rooms live in the students table.
CREATE TABLE students (
  roll INTEGER PRIMARY KEY, name TEXT NOT NULL, dept TEXT NOT NULL,
  hostel TEXT, room INTEGER
);
INSERT INTO students VALUES
  (101, 'Asha', 'CSE', 'Kaveri', 12), (102, 'Ravi', 'ECE', NULL, NULL),
  (103, 'Meera', 'CSE', 'Godavari', 7);

-- A report that applications already run.
SELECT name, hostel, room FROM students WHERE dept = 'CSE' ORDER BY roll;

-- Second design: rooms move to a table of their own.
CREATE TABLE people (roll INTEGER PRIMARY KEY, name TEXT NOT NULL, dept TEXT NOT NULL);
CREATE TABLE rooms (
  roll INTEGER PRIMARY KEY REFERENCES people (roll),
  hostel TEXT NOT NULL, room INTEGER NOT NULL
);
INSERT INTO people SELECT roll, name, dept FROM students;
INSERT INTO rooms SELECT roll, hostel, room FROM students WHERE hostel IS NOT NULL;
DROP TABLE students;

-- A view with the old name and the old columns.
CREATE VIEW students AS
  SELECT p.roll, p.name, p.dept, r.hostel, r.room
  FROM people AS p LEFT JOIN rooms AS r ON r.roll = p.roll;

-- The same report, unchanged, gives the same answer.
SELECT name, hostel, room FROM students WHERE dept = 'CSE' ORDER BY roll;

┌───────┬──────────┬──────┐
│ name  │  hostel  │ room │
├───────┼──────────┼──────┤
│ Asha  │ Kaveri   │ 12   │
│ Meera │ Godavari │ 7    │
└───────┴──────────┴──────┘
┌───────┬──────────┬──────┐
│ name  │  hostel  │ room │
├───────┼──────────┼──────┤
│ Asha  │ Kaveri   │ 12   │
│ Meera │ Godavari │ 7    │
└───────┴──────────┴──────┘

The report printed the same three rows before and after. Ravi has no room, so the view’s LEFT JOIN keeps him with empty hostel columns, exactly as the old table did, and the report never mentions people or rooms.

Naming a query. A join of five tables with the right conditions, written once as a view, cannot be got wrong in the next report that needs it.

Changing rows through a view

PostgreSQL makes a view automatically updatable when it meets all of these conditions:

• its FROM list has exactly one entry, a table or another updatable view;
• it has no WITH, DISTINCT, GROUP BY, HAVING, LIMIT or OFFSET at the top level;
• it has no UNION, INTERSECT or EXCEPT at the top level;
• its select list has no aggregates, window functions or set-returning functions.

Then INSERT, UPDATE, DELETE and MERGE on the view become the same statements on the table. A column of the view is updatable when it is a plain reference to a column of the table; a computed column such as upper(name) is read-only. An INSERT can fill only the view’s columns and the rest get their defaults, so a view that leaves out a NOT NULL column without a default cannot accept any INSERT at all.

# Changing rows through views in PostgreSQL 18.
from pg_session import psql

psql("""
CREATE TABLE students (
  roll integer PRIMARY KEY,
  name text NOT NULL,
  dept text NOT NULL,
  cgpa numeric(3,1)
);
INSERT INTO students VALUES
  (101, 'Asha', 'CSE', 8.9), (102, 'Ravi', 'ECE', 7.4),
  (103, 'Meera', 'CSE', 9.2), (104, 'Kabir', 'CSE', 6.8);

CREATE VIEW cse_students AS
  SELECT roll, name, dept FROM students WHERE dept = 'CSE';
CREATE VIEW cse_checked AS
  SELECT roll, name, dept FROM students WHERE dept = 'CSE'
  WITH CHECK OPTION;
CREATE VIEW dept_sizes AS
  SELECT dept, count(*) AS students FROM students GROUP BY dept;

SELECT table_name, is_updatable, is_insertable_into
FROM information_schema.views WHERE table_schema = 'public' ORDER BY table_name;

UPDATE cse_students SET name = 'Asha Rao' WHERE roll = 101;   -- a CSE row: changed
UPDATE cse_students SET name = 'Ravi K' WHERE roll = 102;     -- ECE: not in the view
UPDATE cse_students SET dept = 'ECE' WHERE roll = 103;        -- allowed: Meera leaves
SELECT * FROM cse_students ORDER BY roll;

UPDATE cse_checked SET dept = 'ME' WHERE roll = 104;
INSERT INTO cse_checked VALUES (105, 'Neel', 'ECE');
INSERT INTO cse_checked VALUES (105, 'Neel', 'CSE');
UPDATE dept_sizes SET students = 0;
SELECT * FROM students ORDER BY roll;
""")

CREATE TABLE
INSERT 0 4
CREATE VIEW
CREATE VIEW
CREATE VIEW
  table_name  | is_updatable | is_insertable_into 
--------------+--------------+--------------------
 cse_checked  | YES          | YES
 cse_students | YES          | YES
 dept_sizes   | NO           | NO
(3 rows)

UPDATE 1
UPDATE 0
UPDATE 1
 roll |   name   | dept 
------+----------+------
  101 | Asha Rao | CSE
  104 | Kabir    | CSE
(2 rows)

ERROR:  new row violates check option for view "cse_checked"
DETAIL:  Failing row contains (104, Kabir, ME, 6.8).
ERROR:  new row violates check option for view "cse_checked"
DETAIL:  Failing row contains (105, Neel, ECE, null).
INSERT 0 1
ERROR:  cannot update view "dept_sizes"
DETAIL:  Views containing GROUP BY are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
 roll |   name   | dept | cgpa 
------+----------+------+------
  101 | Asha Rao | CSE  |  8.9
  102 | Ravi     | ECE  |  7.4
  103 | Meera    | ECE  |  9.2
  104 | Kabir    | CSE  |  6.8
  105 | Neel     | CSE  |     
(5 rows)

Read the output from the top:

• information_schema.views already says which views accept changes: the two simple ones do, the grouped one does not.
• UPDATE 1, UPDATE 0, UPDATE 1: the view’s WHERE decides which rows a statement can reach. Ravi is in ECE, so the second update found no row to change.
• The third update moved Meera to ECE. That was allowed, and she vanished from cse_students: a change made through a view may produce a row that the view no longer shows.
• cse_checked was created WITH CHECK OPTION, so the same kind of change was refused, and so was the insert of an ECE student. The insert of a CSE student went through, with cgpa left NULL because the view does not have it.
• dept_sizes groups its rows, so it is read-only, and the error says what would make it writable: an INSTEAD OF trigger, or a rule.

WITH CHECK OPTION: LOCAL and CASCADED

WITH CHECK OPTION checks that every row an INSERT, UPDATE or MERGE produces through the view is visible through the view. When one view is built on another, the option has two forms. LOCAL checks only the conditions written in the view itself; CASCADED, which is what WITH CHECK OPTION means on its own, also checks the conditions of every view underneath.

# WITH LOCAL CHECK OPTION against WITH CASCADED CHECK OPTION, on a view built on a view.
from pg_session import psql

psql("""
CREATE TABLE students (roll integer PRIMARY KEY, name text NOT NULL, dept text NOT NULL, cgpa numeric(3,1));
CREATE VIEW cse AS SELECT * FROM students WHERE dept = 'CSE';          -- no check option
CREATE VIEW toppers_local AS SELECT * FROM cse WHERE cgpa >= 9
  WITH LOCAL CHECK OPTION;
CREATE VIEW toppers_cascaded AS SELECT * FROM cse WHERE cgpa >= 9
  WITH CASCADED CHECK OPTION;

INSERT INTO toppers_local VALUES (201, 'Tara', 'ECE', 9.5);      -- only cgpa is checked
INSERT INTO toppers_cascaded VALUES (202, 'Uday', 'ECE', 9.5);   -- dept is checked too
INSERT INTO toppers_cascaded VALUES (203, 'Vani', 'CSE', 8.0);
INSERT INTO toppers_cascaded VALUES (204, 'Wasim', 'CSE', 9.1);

SELECT * FROM students ORDER BY roll;
SELECT count(*) AS tara_in_toppers_local FROM toppers_local WHERE roll = 201;
""")

CREATE TABLE
CREATE VIEW
CREATE VIEW
CREATE VIEW
INSERT 0 1
ERROR:  new row violates check option for view "cse"
DETAIL:  Failing row contains (202, Uday, ECE, 9.5).
ERROR:  new row violates check option for view "toppers_cascaded"
DETAIL:  Failing row contains (203, Vani, CSE, 8.0).
INSERT 0 1
 roll | name  | dept | cgpa 
------+-------+------+------
  201 | Tara  | ECE  |  9.5
  204 | Wasim | CSE  |  9.1
(2 rows)

 tara_in_toppers_local 
-----------------------
                     0
(1 row)

toppers_local checked only cgpa >= 9, so Tara, an ECE student, was inserted through a view of CSE toppers. The last query shows the result: she is in the table, and invisible through the very view that inserted her. The cascaded view refused Uday because of the condition of cse, the view underneath, and its error names that view; it refused Vani because of its own condition, and accepted Wasim, who meets both. Use CASCADED unless you have a reason not to.

SQLite: read-only views and INSTEAD OF triggers

In SQLite every view is read-only. An INSTEAD OF trigger on a view tells SQLite what to do when a statement tries to change it: the trigger’s statements run instead, once for each row of the view the statement would have changed.

CREATE TABLE students (
  roll INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);
INSERT INTO students VALUES (101, 'Asha', 'CSE'), (102, 'Ravi', 'ECE');
CREATE VIEW cse_students AS
  SELECT roll, name, dept FROM students WHERE dept = 'CSE';

-- Refused: in SQLite every view is read-only.
UPDATE cse_students SET name = 'Asha Rao' WHERE roll = 101;

-- An INSTEAD OF trigger says what an UPDATE of the view should do instead.
CREATE TRIGGER cse_students_update
INSTEAD OF UPDATE ON cse_students
BEGIN
  -- Refuse a change that would take the row out of the view.
  SELECT RAISE(ABORT, 'the row would leave cse_students')
  WHERE NEW.dept <> 'CSE';
  UPDATE students SET name = NEW.name, dept = NEW.dept
  WHERE roll = OLD.roll;
END;

UPDATE cse_students SET name = 'Asha Rao' WHERE roll = 101;
UPDATE cse_students SET dept = 'ME' WHERE roll = 101;
UPDATE cse_students SET name = 'nobody' WHERE roll = 102;
SELECT * FROM students ORDER BY roll;

┌──────┬──────────┬──────┐
│ roll │   name   │ dept │
├──────┼──────────┼──────┤
│ 101  │ Asha Rao │ CSE  │
│ 102  │ Ravi     │ ECE  │
└──────┴──────────┴──────┘

Parse error near line 11: cannot modify cse_students because it is a view
Runtime error near line 25: the row would leave cse_students

The first update was refused with “cannot modify cse_students because it is a view”. After the trigger existed, the name change went through. The move to ME was stopped by the trigger’s own check, which plays the part of a check option: RAISE(ABORT, …) ends the statement with an error and undoes what it had changed. The update of roll 102 did nothing, because Ravi is not in the view, so the trigger never fired for him.

PostgreSQL has INSTEAD OF triggers too, and they are how a view it cannot update by itself, such as dept_sizes, is made writable. A check option on a view that a trigger updates is ignored, so the trigger has to make its own checks, as this one does. The triggers lesson covers triggers in full.

Materialized views store the result

A materialized view runs its query when it is created and stores the rows. Reading it reads those rows, like a table. Here is what one read costs each way, for the same grouping of 100,000 enrolments:

# The work one read does: a view runs its query, a materialized view reads stored rows.
from pg_session import psql

psql("""
CREATE TABLE enrolments (roll integer, course text, PRIMARY KEY (roll, course));
INSERT INTO enrolments
  SELECT g, (ARRAY['DBMS', 'OS', 'CN', 'TOC'])[1 + g % 4] FROM generate_series(1, 100000) AS g;
ANALYZE enrolments;

CREATE VIEW course_sizes_v AS
  SELECT course, count(*) AS students FROM enrolments GROUP BY course;
CREATE MATERIALIZED VIEW course_sizes_m AS
  SELECT course, count(*) AS students FROM enrolments GROUP BY course;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM course_sizes_v;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM course_sizes_m;
""")

CREATE TABLE
INSERT 0 100000
ANALYZE
CREATE VIEW
SELECT 4
                          QUERY PLAN                          
--------------------------------------------------------------
 HashAggregate (actual rows=4.00 loops=1)
   Group Key: enrolments.course
   Batches: 1  Memory Usage: 32kB
   ->  Seq Scan on enrolments (actual rows=100000.00 loops=1)
(4 rows)

                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on course_sizes_m (actual rows=4.00 loops=1)
(1 row)

To return four rows, the view scanned all 100,000 enrolments and grouped them; the materialized view scanned its four stored rows. That saving is the reason to use one. The price is freshness:

# A materialized view keeps its rows until REFRESH computes them again.
from pg_session import psql

psql("""
CREATE TABLE enrolments (roll integer, course text, PRIMARY KEY (roll, course));
INSERT INTO enrolments VALUES (101, 'DBMS'), (102, 'DBMS'), (101, 'OS'), (103, 'CN');

CREATE MATERIALIZED VIEW course_sizes AS
  SELECT course, count(*) AS students FROM enrolments GROUP BY course;

INSERT INTO enrolments VALUES (103, 'DBMS'), (104, 'OS');
SELECT * FROM course_sizes ORDER BY course;      -- still the old counts

REFRESH MATERIALIZED VIEW CONCURRENTLY course_sizes;
CREATE UNIQUE INDEX ON course_sizes (course);
REFRESH MATERIALIZED VIEW CONCURRENTLY course_sizes;
SELECT * FROM course_sizes ORDER BY course;

UPDATE course_sizes SET students = 0;

-- The locks each kind of refresh holds on the materialized view until COMMIT.
BEGIN;
REFRESH MATERIALIZED VIEW course_sizes;
SELECT string_agg(mode, ', ' ORDER BY mode) AS plain_refresh_holds
FROM pg_locks WHERE relation = 'course_sizes'::regclass AND pid = pg_backend_pid();
COMMIT;
BEGIN;
REFRESH MATERIALIZED VIEW CONCURRENTLY course_sizes;
SELECT string_agg(mode, ', ' ORDER BY mode) AS concurrent_refresh_holds
FROM pg_locks WHERE relation = 'course_sizes'::regclass AND pid = pg_backend_pid();
COMMIT;

CREATE MATERIALIZED VIEW later AS SELECT * FROM enrolments WITH NO DATA;
SELECT count(*) FROM later;
""")

CREATE TABLE
INSERT 0 4
SELECT 3
INSERT 0 2
 course | students 
--------+----------
 CN     |        1
 DBMS   |        2
 OS     |        1
(3 rows)

ERROR:  cannot refresh materialized view "public.course_sizes" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.
CREATE INDEX
REFRESH MATERIALIZED VIEW
 course | students 
--------+----------
 CN     |        1
 DBMS   |        3
 OS     |        2
(3 rows)

ERROR:  cannot change materialized view "course_sizes"
BEGIN
REFRESH MATERIALIZED VIEW
              plain_refresh_holds              
-----------------------------------------------
 AccessExclusiveLock, ExclusiveLock, ShareLock
(1 row)

COMMIT
BEGIN
REFRESH MATERIALIZED VIEW
             concurrent_refresh_holds             
--------------------------------------------------
 AccessShareLock, ExclusiveLock, RowExclusiveLock
(1 row)

COMMIT
CREATE MATERIALIZED VIEW
ERROR:  materialized view "later" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.

• After two new enrolments the stored counts were still the old ones, until a refresh ran the query again.
• REFRESH MATERIALIZED VIEW replaces the stored rows completely. The CONCURRENTLY form was refused at first: it needs a UNIQUE index on plain columns of the materialized view, with no WHERE clause and no expressions, so that it can match old rows with new ones. With the index it worked. It also needs the view to be populated already, and only one refresh of a materialized view can run at a time.
• A materialized view cannot be changed directly; only a refresh changes it.
• The locks explain when to choose which refresh. A plain refresh holds an ACCESS EXCLUSIVE lock until the transaction ends, and that lock blocks even a plain SELECT of the view. The concurrent refresh’s strongest lock is EXCLUSIVE, which still lets other sessions read the old rows while the new ones are computed. A plain refresh does less work when most rows change; a concurrent one keeps readers going and is often faster when few rows change.
• Created WITH NO DATA, a materialized view cannot be read until its first refresh. Running REFRESH needs the MAINTAIN privilege on the materialized view, so a scheduled job can be given exactly that.

Materialized results in SQLite

SQLite has no materialized views, so you build the effect yourself: a table for the result, and either a scheduled rebuild (delete its rows and insert the query’s result again, in one transaction) or triggers that keep it current as the data changes.

CREATE TABLE enrolments (
  roll   INTEGER NOT NULL,
  course TEXT NOT NULL,
  PRIMARY KEY (roll, course)
);
-- The stored result: one row per course, kept up to date by two triggers.
CREATE TABLE course_sizes (
  course   TEXT PRIMARY KEY,
  students INTEGER NOT NULL
);
CREATE TRIGGER enrolment_added AFTER INSERT ON enrolments
BEGIN
  INSERT INTO course_sizes VALUES (NEW.course, 1)
  ON CONFLICT (course) DO UPDATE SET students = students + 1;
END;
CREATE TRIGGER enrolment_removed AFTER DELETE ON enrolments
BEGIN
  UPDATE course_sizes SET students = students - 1 WHERE course = OLD.course;
  DELETE FROM course_sizes WHERE course = OLD.course AND students = 0;
END;

INSERT INTO enrolments VALUES (101, 'DBMS'), (102, 'DBMS'), (101, 'OS'), (103, 'CN');
INSERT INTO enrolments VALUES (103, 'DBMS'), (104, 'OS');
DELETE FROM enrolments WHERE course = 'CN';
SELECT * FROM course_sizes ORDER BY course;

-- The same numbers computed from scratch: what a full rebuild would store.
SELECT course, count(*) AS students FROM enrolments GROUP BY course ORDER BY course;

┌────────┬──────────┐
│ course │ students │
├────────┼──────────┤
│ DBMS   │ 3        │
│ OS     │ 2        │
└────────┴──────────┘
┌────────┬──────────┐
│ course │ students │
├────────┼──────────┤
│ DBMS   │ 3        │
│ OS     │ 2        │
└────────┴──────────┘

Each insert adds one to its course, using ON CONFLICT … DO UPDATE to create the course’s row the first time, and each delete takes one away and removes a course that reaches zero. The last query computes the counts from scratch and agrees. Triggers like these have to cover every way the data can change: an UPDATE that moves an enrolment to another course would make the counts wrong here, because no trigger handles it.

Exercise: a view from a written specification

-- Create the view here: roll, name and dept of the students on the shortlist.
CREATE VIEW scholarship_shortlist AS
SELECT roll, name, dept, cgpa, phone
FROM students;

-- The check reads the view. Leave this line as it is.
SELECT * FROM scholarship_shortlist ORDER BY roll;

# Sample tests: each one builds a small students table, runs your file (the view and the line that reads it) and
# compares the rows that line returns, in order.
tests:
  - name: the students above both limits, and only the three columns
    ordered: true
    setup: |
      CREATE TABLE students (roll INTEGER PRIMARY KEY, name TEXT, dept TEXT, cgpa REAL, attendance INTEGER, phone TEXT);
      INSERT INTO students VALUES
        (101, 'Asha',  'CSE', 9.1, 92, '90000 00101'),
        (102, 'Ravi',  'ECE', 8.7, 61, '90000 00102'),
        (103, 'Meera', 'CSE', 7.9, 98, '90000 00103'),
        (104, 'Kabir', 'ME',  8.8, 80, '90000 00104');
    expected:
      columns: [roll, name, dept]
      rows:
        - [101, 'Asha', 'CSE']
        - [104, 'Kabir', 'ME']
  - name: a cgpa of exactly 8.5 and an attendance of exactly 75 are enough
    ordered: true
    setup: |
      CREATE TABLE students (roll INTEGER PRIMARY KEY, name TEXT, dept TEXT, cgpa REAL, attendance INTEGER, phone TEXT);
      INSERT INTO students VALUES
        (201, 'Tara',  'CSE', 8.5, 75, '90000 00201'),
        (202, 'Uday',  'ECE', 8.4, 99, '90000 00202'),
        (203, 'Vani',  'IT',  9.6, 74, '90000 00203'),
        (204, 'Wasim', 'CSE', 8.5, 100, '90000 00204');
    expected:
      columns: [roll, name, dept]
      rows:
        - [201, 'Tara', 'CSE']
        - [204, 'Wasim', 'CSE']
  - name: attendance that was not recorded keeps a student off the list
    ordered: true
    setup: |
      CREATE TABLE students (roll INTEGER PRIMARY KEY, name TEXT, dept TEXT, cgpa REAL, attendance INTEGER, phone TEXT);
      INSERT INTO students VALUES
        (301, 'Zoya', 'CSE', 9.9, NULL, '90000 00301'),
        (302, 'Neel', 'ECE', 9.0, 88, '90000 00302'),
        (303, 'Isha', 'ME', NULL, 90, '90000 00303');
    expected:
      columns: [roll, name, dept]
      rows:
        - [302, 'Neel', 'ECE']

Check yourself

Interview questions

What is the difference between a view and a materialized view? (warm-up)

A view stores only its query. Every read runs that query against the base tables, so the result is always current, it costs a full query each time, and nothing has to be refreshed. A materialized view stores the query and its result rows. Reads scan the stored rows, which is much cheaper for a heavy aggregate, but the rows are only as fresh as the last REFRESH MATERIALIZED VIEW, and the materialized view can have indexes of its own. In PostgreSQL a plain refresh locks out readers while it runs, and REFRESH … CONCURRENTLY keeps them reading but needs a unique index. SQLite has views but no materialized views; a table kept current by triggers, or rebuilt on a schedule, takes their place.

Key takeaways

• A view stores a query, never rows. PostgreSQL rewrites a query on a view into a query on the tables, so a view costs nothing extra at run time.
• Views restrict what users see, keep old queries working while tables change, and give long queries a name. List the view’s columns instead of writing *: PostgreSQL fixes * when the view is created, SQLite reads it again.
• PostgreSQL updates through a view by itself when the view reads one table without grouping, DISTINCT, set operations or aggregates; only plain columns are updatable.
• Without a check option, a change through a view may create rows the view cannot show. WITH CHECK OPTION refuses them; CASCADED, the default, also checks the views underneath, and LOCAL does not.
• SQLite views are read-only; INSTEAD OF triggers make them writable, and their checks are your own.
• A materialized view stores its result and is stale until REFRESH. A plain refresh blocks readers; CONCURRENTLY lets them read but needs a populated view and a unique index on plain columns.