Views and updatable views

A view is a query with a name. CREATE VIEW active_customers AS SELECT … stores the query in the database, and from then on active_customers can be read like a table: in FROM, in a join, in a subquery. A view stores no rows. Each time something reads it, the engine runs the view’s query against the tables underneath, so a view always shows the current data and costs exactly what its query costs. Views give a long join or filter one short name, show some users only some columns or rows, and keep an old table name working after the schema changes. PostgreSQL, MySQL and SQLite all have them; they differ in how a view is changed and in whether rows can be written through it. PostgreSQL and MySQL let you insert, update and delete through simple views. SQLite views are read-only, unless INSTEAD OF triggers say what a write means.

You can run the SQLite examples on this page in your browser (sql.js 1.14.2, which is SQLite 3.49.1). The others were run on disposable local servers, PostgreSQL 18.6 and MySQL 8.4.11, and show the output recorded there.

A view is a stored query

Six customers of a small online shop, and a view of the ones whose account is open:

-- Six customers of a small online shop; active = 0 marks a closed account.
CREATE TABLE customers (
  id     INTEGER PRIMARY KEY,
  name   TEXT    NOT NULL,
  city   TEXT    NOT NULL,
  phone  TEXT,
  active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1))
);
INSERT INTO customers VALUES
  (1, 'Asha',     'Pune',     '9800000001', 1),
  (2, 'Farhan',   'Lucknow',  '9800000002', 0),
  (3, 'Meera',    'Kochi',    NULL,         1),
  (4, 'Joseph',   'Pune',     '9800000004', 1),
  (5, 'Tara',     'Jaipur',   '9800000005', 0),
  (6, 'Gurpreet', 'Ludhiana', '9800000006', 1);

-- The view keeps this query under a name. It keeps no rows.
CREATE VIEW active_customers AS
SELECT id, name, city
FROM customers
WHERE active = 1;

SELECT * FROM active_customers ORDER BY id;

-- A new customer signs up. Nothing refreshes the view: the next read runs its query again.
INSERT INTO customers (id, name, city, phone) VALUES (7, 'Lakshmi', 'Madurai', '9800000007');
SELECT * FROM active_customers ORDER BY id;

-- All that SQLite stores for the view: its name and its SQL text.
SELECT type, name, sql FROM sqlite_schema WHERE name = 'active_customers';

┌────┬──────────┬──────────┐
│ id │   name   │   city   │
├────┼──────────┼──────────┤
│ 1  │ Asha     │ Pune     │
│ 3  │ Meera    │ Kochi    │
│ 4  │ Joseph   │ Pune     │
│ 6  │ Gurpreet │ Ludhiana │
└────┴──────────┴──────────┘
┌────┬──────────┬──────────┐
│ id │   name   │   city   │
├────┼──────────┼──────────┤
│ 1  │ Asha     │ Pune     │
│ 3  │ Meera    │ Kochi    │
│ 4  │ Joseph   │ Pune     │
│ 6  │ Gurpreet │ Ludhiana │
│ 7  │ Lakshmi  │ Madurai  │
└────┴──────────┴──────────┘
┌──────┬──────────────────┬─────────────────────────────────┐
│ type │       name       │               sql               │
├──────┼──────────────────┼─────────────────────────────────┤
│ view │ active_customers │ CREATE VIEW active_customers AS │
│      │                  │ SELECT id, name, city           │
│      │                  │ FROM customers                  │
│      │                  │ WHERE active = 1                │
└──────┴──────────────────┴─────────────────────────────────┘

The view returns four of the six customers and three of their five columns. When Lakshmi signs up, the next read of the view includes her. Nothing was refreshed, because there is nothing to refresh: the view keeps no copy of the data. The last query shows everything SQLite keeps for a view, one row in sqlite_schema with its type, its name and the text of its CREATE VIEW statement.

A view therefore saves typing, not work. A report that reads active_customers a thousand times runs its query a thousand times. When reading a summary has to be cheap, the result itself must be stored, which is what the next lesson, on materialized views and summary tables, is about.

Name the columns of a view

A view’s columns come from its query, and the query depends on the tables underneath. Three views of the same customers show what a routine migration does to each kind:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL, phone TEXT);
INSERT INTO customers VALUES (1, 'Asha', 'Pune', '9800000001'), (3, 'Meera', 'Kochi', NULL);

-- Three views of the same columns: with SELECT *, with the table's column names, and with a column list.
CREATE VIEW v_star  AS SELECT * FROM customers;
CREATE VIEW v_plain AS SELECT id, name, city FROM customers;
CREATE VIEW v_named (customer_id, name, city) AS SELECT id, name, city FROM customers;

-- A migration adds a column and renames another.
ALTER TABLE customers ADD COLUMN email TEXT;
ALTER TABLE customers RENAME COLUMN city TO town;

SELECT * FROM v_star;
SELECT * FROM v_plain;
SELECT * FROM v_named;

-- SQLite rewrote the stored SQL of every view that named the column.
SELECT name, sql FROM sqlite_schema WHERE type = 'view' ORDER BY name;

┌────┬───────┬───────┬────────────┬───────┐
│ id │ name  │ town  │   phone    │ email │
├────┼───────┼───────┼────────────┼───────┤
│ 1  │ Asha  │ Pune  │ 9800000001 │       │
│ 3  │ Meera │ Kochi │            │       │
└────┴───────┴───────┴────────────┴───────┘
┌────┬───────┬───────┐
│ id │ name  │ town  │
├────┼───────┼───────┤
│ 1  │ Asha  │ Pune  │
│ 3  │ Meera │ Kochi │
└────┴───────┴───────┘
┌─────────────┬───────┬───────┐
│ customer_id │ name  │ city  │
├─────────────┼───────┼───────┤
│ 1           │ Asha  │ Pune  │
│ 3           │ Meera │ Kochi │
└─────────────┴───────┴───────┘
┌─────────┬───────────────────────────────────────────────────────────────────────────────────────┐
│  name   │                                          sql                                          │
├─────────┼───────────────────────────────────────────────────────────────────────────────────────┤
│ v_named │ CREATE VIEW v_named (customer_id, name, city) AS SELECT id, name, town FROM customers │
│ v_plain │ CREATE VIEW v_plain AS SELECT id, name, town FROM customers                           │
│ v_star  │ CREATE VIEW v_star  AS SELECT * FROM customers                                        │
└─────────┴───────────────────────────────────────────────────────────────────────────────────────┘

• v_star now has five columns. SQLite expands * each time it reads the view, so the new email column appeared, and city turned into town. A program that expected four columns, or read them by position, now gets the wrong data or an error.
• v_plain kept three columns, but SQLite rewrote its stored query to read town, so the view’s column is now called town as well. A report that asks the view for city fails.
• v_named still shows customer_id, name and city. The column list after the view’s name fixed the names; only the query underneath changed.

PostgreSQL and MySQL expand * once, when the view is created, and they disagree about what happens next:

# The migration of the SQLite example, on PostgreSQL 18.6: views keep the columns they had when they were created.
from pg_session import psql

psql("""
CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL, city text NOT NULL, phone text);
INSERT INTO customers VALUES (1, 'Asha', 'Pune', '9800000001'), (3, 'Meera', 'Kochi', NULL);
CREATE VIEW v_star AS SELECT * FROM customers;
CREATE VIEW v_plain AS SELECT id, name, city FROM customers;

-- A new column does not reach a SELECT * view: * was expanded when the view was created.
ALTER TABLE customers ADD COLUMN email text;
SELECT * FROM v_star;

-- A renamed column keeps its old name in the views: they now read town AS city.
ALTER TABLE customers RENAME COLUMN city TO town;
SELECT * FROM v_plain;
SELECT pg_get_viewdef('v_plain');

-- A column that a view uses cannot be dropped without CASCADE, which would drop the view as well.
ALTER TABLE customers DROP COLUMN phone;

-- CREATE OR REPLACE VIEW may add columns at the end, never remove or reorder them.
CREATE OR REPLACE VIEW v_plain AS SELECT id, name, town AS city, email FROM customers;
CREATE OR REPLACE VIEW v_plain AS SELECT id, name FROM customers;
""")

CREATE TABLE
INSERT 0 2
CREATE VIEW
CREATE VIEW
ALTER TABLE
 id | name  | city  |   phone    
----+-------+-------+------------
  1 | Asha  | Pune  | 9800000001
  3 | Meera | Kochi | 
(2 rows)

ALTER TABLE
 id | name  | city  
----+-------+-------
  1 | Asha  | Pune
  3 | Meera | Kochi
(2 rows)

   pg_get_viewdef   
--------------------
  SELECT id,       +
     name,         +
     town AS city  +
    FROM customers;
(1 row)

ERROR:  cannot drop column phone of table customers because other objects depend on it
DETAIL:  view v_star depends on column phone of table customers
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
CREATE VIEW
ERROR:  cannot drop columns from view

# The same migration on MySQL 8.4.11: the view's columns are frozen, and a dropped column breaks it.
from mysql_session import mysql

mysql("""
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(40) NOT NULL, city VARCHAR(40) NOT NULL, phone CHAR(10));
INSERT INTO customers VALUES (1, 'Asha', 'Pune', '9800000001'), (3, 'Meera', 'Kochi', NULL);
CREATE VIEW v_star AS SELECT * FROM customers;

-- A new column does not reach the view: its column list was fixed when it was created.
ALTER TABLE customers ADD COLUMN email VARCHAR(80);
SELECT * FROM v_star;

-- Dropping a column the view reads is allowed; the view fails from then on, and CHECK TABLE says so.
ALTER TABLE customers DROP COLUMN phone;
SELECT * FROM v_star;
CHECK TABLE v_star;
""")

+----+-------+-------+------------+
| id | name  | city  | phone      |
+----+-------+-------+------------+
|  1 | Asha  | Pune  | 9800000001 |
|  3 | Meera | Kochi | NULL       |
+----+-------+-------+------------+
ERROR 1356 (HY000) at line 11: View 'shop.v_star' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+-------------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Table       | Op    | Msg_type | Msg_text                                                                                                                      |
+-------------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------+
| shop.v_star | check | Error    | View 'shop.v_star' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
| shop.v_star | check | error    | Corrupt                                                                                                                       |
+-------------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------+

In both, the new email column never reached v_star. PostgreSQL keeps track of what each view uses: after the rename, v_plain reads town AS city, so its readers still find city, and dropping phone is refused because v_star depends on it. MySQL lets the column go, and from then on every read of v_star fails with error 1356, which CHECK TABLE also reports. The same SELECT * view gives different columns in three engines after one migration. Name the columns, with a list after the view’s name or an alias for each one, and they become a fixed part of what the view promises its readers.

Changing or dropping a view

PostgreSQL and MySQL replace a view in place with CREATE OR REPLACE VIEW. PostgreSQL accepts the new query only if it returns the same columns, with the same names, in the same order and with the same types; it may add columns at the end, and the PostgreSQL run above shows the refusal when a column is left out. Replacing keeps the view’s owner and privileges. MySQL also has ALTER VIEW. SQLite has neither: you run DROP VIEW and then CREATE VIEW, inside one transaction so that no other connection reads the schema between them.

SQLite also checks a view later than the other two engines. It creates a view over a table that does not exist at all:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
INSERT INTO customers VALUES (1, 'Asha', 'Pune'), (3, 'Meera', 'Kochi');

-- SQLite accepts a view over a table that does not exist (a typo, or a table dropped long ago) ...
CREATE VIEW old_report AS SELECT id, name FROM customers_2025;

-- ... and reports the problem only when the view is read, or when any table is altered:
SELECT * FROM old_report;
ALTER TABLE customers RENAME COLUMN city TO town;

-- Dropping the broken view lets the migration run.
DROP VIEW old_report;
ALTER TABLE customers RENAME COLUMN city TO town;

-- A view that names a column keeps that column from being dropped.
CREATE VIEW towns AS SELECT DISTINCT town FROM customers;
ALTER TABLE customers DROP COLUMN town;
SELECT * FROM towns ORDER BY town;

┌───────┐
│ town  │
├───────┤
│ Kochi │
│ Pune  │
└───────┘

Parse error near line 8: no such table: main.customers_2025
Runtime error near line 9: error in view old_report: no such table: main.customers_2025
Runtime error near line 17: error in view towns after drop column: no such column: town

SQLite created old_report without a complaint, perhaps from a typo or a table removed long ago. Reading it fails, and so does the ALTER TABLE on customers, although old_report never reads customers: SQLite checks the views of the whole schema when it renames or drops a column. Once the broken view is gone, the rename works. The last error is a useful one. A view that names a column keeps that column from being dropped, as in PostgreSQL.

Writing through a view

An INSERT, UPDATE or DELETE on a view must become a change to rows of a table, and the engine can only do that when every row of the view stands for exactly one row of one table.

• PostgreSQL makes a view automatically updatable when its FROM has exactly one table or updatable view and it has no WITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET, UNION, INTERSECT or EXCEPT at the top level and no aggregate, window function or set-returning function in its select list. A computed column, such as upper(name), stays read-only, while the plain columns beside it can still be written.
• MySQL asks for the same one-to-one relationship. A view with an aggregate, DISTINCT, GROUP BY, HAVING, UNION, a subquery in its select list or ALGORITHM = TEMPTABLE is not updatable. A view that joins tables with an inner join can take an UPDATE of one table’s columns and an INSERT into one table, but never a DELETE.
• SQLite never writes through a view by itself. Its views are read-only.

The Pune support desk below works through a view that shows only Pune’s customers, first in PostgreSQL and MySQL:

# Writing through an automatically updatable view on PostgreSQL 18.6, without and with WITH CHECK OPTION.
from pg_session import psql

psql("""
CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL, city text NOT NULL, phone text);
INSERT INTO customers VALUES (1, 'Asha', 'Pune', '9800000001'), (2, 'Farhan', 'Lucknow', NULL), (4, 'Joseph', 'Pune', '9800000004');
CREATE VIEW pune_customers AS SELECT id, name, city, phone FROM customers WHERE city = 'Pune';

-- One table, no grouping: PostgreSQL turns each write on the view into a write on customers.
UPDATE pune_customers SET phone = '9800000011' WHERE id = 1;
-- The WHERE only chooses the rows a write can reach: these two rows leave the view, or never enter it.
UPDATE pune_customers SET city = 'Mumbai' WHERE id = 4;
INSERT INTO pune_customers VALUES (9, 'Kiran', 'Delhi', NULL);
SELECT * FROM pune_customers ORDER BY id;
SELECT * FROM customers ORDER BY id;

-- With CHECK OPTION, a new or changed row must still be one the view shows.
CREATE OR REPLACE VIEW pune_customers AS
SELECT id, name, city, phone FROM customers WHERE city = 'Pune'
WITH CHECK OPTION;
UPDATE pune_customers SET city = 'Mumbai' WHERE id = 1;
INSERT INTO pune_customers VALUES (10, 'Rohit', 'Delhi', NULL);
INSERT INTO pune_customers VALUES (8, 'Rohit', 'Pune', '9800000008');

-- A grouped view is read-only.
CREATE VIEW city_counts AS SELECT city, count(*) AS customers FROM customers GROUP BY city;
UPDATE city_counts SET customers = 0;
""")

CREATE TABLE
INSERT 0 3
CREATE VIEW
UPDATE 1
UPDATE 1
INSERT 0 1
 id | name | city |   phone    
----+------+------+------------
  1 | Asha | Pune | 9800000011
(1 row)

 id |  name  |  city   |   phone    
----+--------+---------+------------
  1 | Asha   | Pune    | 9800000011
  2 | Farhan | Lucknow | 
  4 | Joseph | Mumbai  | 9800000004
  9 | Kiran  | Delhi   | 
(4 rows)

CREATE VIEW
ERROR:  new row violates check option for view "pune_customers"
DETAIL:  Failing row contains (1, Asha, Mumbai, 9800000011).
ERROR:  new row violates check option for view "pune_customers"
DETAIL:  Failing row contains (10, Rohit, Delhi, null).
INSERT 0 1
CREATE VIEW
ERROR:  cannot update view "city_counts"
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.

# Which writes MySQL 8.4.11 lets through a view: one table, a join, a GROUP BY, and WITH CHECK OPTION.
from mysql_session import mysql

mysql("""
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(40) NOT NULL, city VARCHAR(40) NOT NULL);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT NOT NULL, total_paise INT NOT NULL);
INSERT INTO customers VALUES (1, 'Asha', 'Pune'), (2, 'Farhan', 'Lucknow'), (4, 'Joseph', 'Pune');
INSERT INTO orders VALUES (10, 1, 50000), (11, 4, 70000);

CREATE VIEW pune_customers AS SELECT id, name, city FROM customers WHERE city = 'Pune' WITH CHECK OPTION;
CREATE VIEW customer_orders AS
SELECT c.id, c.name, o.id AS order_id, o.total_paise FROM customers AS c JOIN orders AS o ON o.customer_id = c.id;
CREATE VIEW city_counts AS SELECT city, COUNT(*) AS customers FROM customers GROUP BY city;
SELECT TABLE_NAME, IS_UPDATABLE, CHECK_OPTION FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'shop' ORDER BY TABLE_NAME;

-- One table with a check option: rows that would leave the view are refused.
UPDATE pune_customers SET name = 'Asha K' WHERE id = 1;
UPDATE pune_customers SET city = 'Mumbai' WHERE id = 4;
INSERT INTO pune_customers VALUES (9, 'Kiran', 'Delhi');

-- A join view: an UPDATE of one table's columns works, of two tables' columns does not, and DELETE never does.
UPDATE customer_orders SET total_paise = 80000 WHERE order_id = 11;
UPDATE customer_orders SET total_paise = 90000, name = 'Joe' WHERE order_id = 11;
DELETE FROM customer_orders WHERE order_id = 10;

-- A GROUP BY view is not updatable, and no view can have a trigger.
UPDATE city_counts SET customers = 0;
CREATE TRIGGER pune_insert BEFORE INSERT ON pune_customers FOR EACH ROW SET NEW.city = 'Pune';
SELECT * FROM customer_orders ORDER BY order_id;
""")

+-----------------+--------------+--------------+
| TABLE_NAME      | IS_UPDATABLE | CHECK_OPTION |
+-----------------+--------------+--------------+
| city_counts     | NO           | NONE         |
| customer_orders | YES          | NONE         |
| pune_customers  | YES          | CASCADED     |
+-----------------+--------------+--------------+
ERROR 1369 (HY000) at line 14: CHECK OPTION failed 'shop.pune_customers'
ERROR 1369 (HY000) at line 15: CHECK OPTION failed 'shop.pune_customers'
ERROR 1393 (HY000) at line 19: Can not modify more than one base table through a join view 'shop.customer_orders'
ERROR 1395 (HY000) at line 20: Can not delete from join view 'shop.customer_orders'
ERROR 1288 (HY000) at line 23: The target table city_counts of the UPDATE is not updatable
ERROR 1347 (HY000) at line 24: 'shop.pune_customers' is not BASE TABLE
+----+--------+----------+-------------+
| id | name   | order_id | total_paise |
+----+--------+----------+-------------+
|  1 | Asha K |       10 |       50000 |
|  4 | Joseph |       11 |       80000 |
+----+--------+----------+-------------+

Without a check option, PostgreSQL accepted all three writes. The view’s WHERE decided which rows the UPDATE could reach, but not what it could change them to, so Joseph moved to Mumbai and out of the view, and Kiran was added in Delhi, where the desk can never see him. WITH CHECK OPTION at the end of CREATE VIEW refuses both kinds of write: a new or changed row must still be one the view shows, or the statement fails, with SQLSTATE 44000 in PostgreSQL and error 1369 in MySQL. LOCAL checks only the conditions of this view; CASCADED, the default in both engines, also checks the views it is built on. The MySQL run also shows the limits of a join view, errors 1393 and 1395, and the grouped view that is not updatable at all, as its IS_UPDATABLE column said beforehand.

INSTEAD OF triggers make a SQLite view writable

An INSTEAD OF trigger runs in place of a write on a view. In its body, NEW holds the row as the statement wrote it to the view and OLD the row as it was, and the trigger decides which tables change. The same desk, in SQLite:

CREATE TABLE customers (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  city  TEXT NOT NULL,
  phone TEXT
);
INSERT INTO customers VALUES
  (1, 'Asha',   'Pune',    '9800000001'),
  (2, 'Farhan', 'Lucknow', '9800000002'),
  (4, 'Joseph', 'Pune',    '9800000004');

-- The Pune support desk works through this view.
CREATE VIEW pune_customers AS
SELECT id, name, city, phone FROM customers WHERE city = 'Pune';

-- 1. Views are read-only in SQLite:
UPDATE pune_customers SET phone = '9800000011' WHERE id = 1;

-- 2. INSTEAD OF triggers turn a write on the view into writes on the table. Each one refuses a row
--    that the view could not show, as WITH CHECK OPTION does in PostgreSQL and MySQL.
CREATE TRIGGER pune_customers_insert INSTEAD OF INSERT ON pune_customers
BEGIN
  SELECT RAISE(ABORT, 'pune_customers: the customer must be in Pune') WHERE NEW.city IS NOT 'Pune';
  INSERT INTO customers (id, name, city, phone) VALUES (NEW.id, NEW.name, NEW.city, NEW.phone);
END;
CREATE TRIGGER pune_customers_update INSTEAD OF UPDATE ON pune_customers
BEGIN
  SELECT RAISE(ABORT, 'pune_customers: the customer must be in Pune') WHERE NEW.city IS NOT 'Pune';
  UPDATE customers SET name = NEW.name, city = NEW.city, phone = NEW.phone WHERE id = OLD.id;
END;

UPDATE pune_customers SET phone = '9800000011' WHERE id = 1;
INSERT INTO pune_customers (id, name, city, phone) VALUES (8, 'Rohit', 'Pune', '9800000008');

-- 3. Writes that would take a row out of the view are refused:
UPDATE pune_customers SET city = 'Mumbai' WHERE id = 4;
INSERT INTO pune_customers (id, name, city, phone) VALUES (9, 'Kiran', 'Delhi', NULL);

SELECT * FROM customers ORDER BY id;

┌────┬────────┬─────────┬────────────┐
│ id │  name  │  city   │   phone    │
├────┼────────┼─────────┼────────────┤
│ 1  │ Asha   │ Pune    │ 9800000011 │
│ 2  │ Farhan │ Lucknow │ 9800000002 │
│ 4  │ Joseph │ Pune    │ 9800000004 │
│ 8  │ Rohit  │ Pune    │ 9800000008 │
└────┴────────┴─────────┴────────────┘

Parse error near line 17: cannot modify pune_customers because it is a view
Runtime error near line 36: pune_customers: the customer must be in Pune
Runtime error near line 37: pune_customers: the customer must be in Pune

The first UPDATE fails with “cannot modify pune_customers because it is a view”. Once the two triggers exist, the same UPDATE changes Asha’s phone number in customers, and the INSERT adds Rohit. The last two writes are refused: each trigger stops with RAISE(ABORT, …), which ends the statement with that message and undoes what it changed, so Joseph still lives in Pune in the final table. That check does the work of WITH CHECK OPTION, which PostgreSQL applies only to automatically updatable views, never to views that use INSTEAD OF triggers.

Views that limit what people can see

In PostgreSQL and MySQL a view can be the only door to a table. Grant the support desk SELECT on the view and nothing on the table, and the desk sees the view’s rows and columns and nothing else:

# A view as the only door to a table, on PostgreSQL 18.6: the support desk may read the view, not the table.
from pg_session import psql

psql("""
CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL, city text NOT NULL, phone text, active boolean NOT NULL);
INSERT INTO customers VALUES (1, 'Asha', 'Pune', '9800000001', true), (2, 'Farhan', 'Lucknow', '9800000002', false),
                             (3, 'Meera', 'Kochi', NULL, true);
CREATE VIEW customer_directory (customer_id, name, city) AS
SELECT id, name, city FROM customers WHERE active;

CREATE ROLE support_desk LOGIN;
GRANT SELECT ON customer_directory TO support_desk;
""")

# The desk reads the view; the view reads customers with its owner's privileges.
psql("""
SELECT * FROM customer_directory ORDER BY customer_id;
SELECT name, phone FROM customers;
""", user="support_desk")

# A security invoker view checks the reader's own privileges on the table instead.
psql("ALTER VIEW customer_directory SET (security_invoker = true);")
psql("SELECT * FROM customer_directory ORDER BY customer_id;", user="support_desk")

CREATE TABLE
INSERT 0 3
CREATE VIEW
CREATE ROLE
GRANT
 customer_id | name  | city  
-------------+-------+-------
           1 | Asha  | Pune
           3 | Meera | Kochi
(2 rows)

ERROR:  permission denied for table customers
ALTER VIEW
ERROR:  permission denied for table customers

The desk read customer_directory and was refused customers itself. That works because, by default, both engines read the tables under a view with the privileges of the view’s owner: PostgreSQL does so unless the view says otherwise, and MySQL through SQL SECURITY DEFINER, its default. The last statement shows the other setting. A PostgreSQL view with security_invoker = true, possible since PostgreSQL 15, checks the privileges and row-level security policies of the user who reads it, so the desk is refused again; MySQL’s SQL SECURITY INVOKER checks the reader’s privileges in the same way. An invoker view is for convenience, not protection. A PostgreSQL view meant to hide rows should also have the security_barrier option, which makes the view’s own conditions run before any condition a reader adds.

Your turn

-- Write the view customer_directory here.


-- Leave these two statements as they are: a customer who signs up after the view exists, then the check.
INSERT INTO customers (id, name, city, phone, active) VALUES (100, 'Nisha', 'Nagpur', '9800000100', 1);
SELECT d.*, (SELECT type FROM sqlite_schema WHERE name = 'customer_directory') AS kind
FROM customer_directory AS d;

# Sample tests: each one fills customers, runs your script and compares what the view returns (in any order), with
# the kind of object customer_directory is.
tests:
  - name: open accounts only, and no phone numbers
    setup: |
      CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, phone TEXT, active INTEGER NOT NULL);
      INSERT INTO customers VALUES
        (1, 'Asha', 'Pune', '9800000001', 1),
        (2, 'Farhan', 'Lucknow', '9800000002', 0),
        (3, 'Meera', 'Kochi', NULL, 1),
        (4, 'Joseph', 'Pune', '9800000004', 1),
        (5, 'Tara', 'Jaipur', '9800000005', 0);
    expected:
      columns: [customer_id, name, city, kind]
      rows:
        - [1, 'Asha', 'Pune', 'view']
        - [3, 'Meera', 'Kochi', 'view']
        - [4, 'Joseph', 'Pune', 'view']
        - [100, 'Nisha', 'Nagpur', 'view']
  - name: a customer without a city stays in the directory
    setup: |
      CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, phone TEXT, active INTEGER NOT NULL);
      INSERT INTO customers VALUES
        (7, 'Lakshmi', NULL, '9800000007', 1),
        (8, 'Rohit', 'Indore', NULL, 0),
        (9, 'Kiran', 'Delhi', NULL, 1);
    expected:
      columns: [customer_id, name, city, kind]
      rows:
        - [7, 'Lakshmi', null, 'view']
        - [9, 'Kiran', 'Delhi', 'view']
        - [100, 'Nisha', 'Nagpur', 'view']
  - name: every account closed before the new sign-up
    setup: |
      CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, phone TEXT, active INTEGER NOT NULL);
      INSERT INTO customers VALUES
        (11, 'Gurpreet', 'Ludhiana', '9800000011', 0),
        (12, 'Sana', 'Srinagar', '9800000012', 0);
    expected:
      columns: [customer_id, name, city, kind]
      rows:
        - [100, 'Nisha', 'Nagpur', 'view']

Interview questions

Fresher · What is a view, and does it store any data?

A view is a named query stored in the database: CREATE VIEW active_customers AS SELECT id, name, city FROM customers WHERE active = 1. Other queries read it like a table, but it stores no rows. Every read runs the view’s query against the tables underneath, so the result is always current and the cost is the cost of that query. What the database keeps is the definition: SQLite stores the text of the CREATE VIEW statement, and PostgreSQL and MySQL list their views with their queries in their catalogs. Views are used to give a complex query a stable name, to expose only some rows or columns to some users, and to keep old names working when tables change. A view that stores its rows is a different object, the materialized view of PostgreSQL, which has to be refreshed.

2 to 5 years · Can you insert or update rows through a view?

Sometimes, and it depends on the engine. PostgreSQL makes a view automatically updatable when it reads exactly one table or updatable view and has no DISTINCT, GROUP BY, HAVING, LIMIT, set operation, aggregate or window function; its computed columns stay read-only. MySQL needs a one-to-one relationship between view rows and table rows, and allows an UPDATE of one table, or an INSERT into one, through an inner-join view. SQLite views are always read-only. For anything else, PostgreSQL and SQLite let an INSTEAD OF trigger say what the write means. Even an updatable view lets an UPDATE move a row out of the view, unless WITH CHECK OPTION refuses it.

Key takeaways

• A view is a stored query, not stored data: every read runs the query, so the view is always current and costs what the query costs.
• Name a view’s columns with a column list or aliases. SQLite expands SELECT * each time it reads the view, PostgreSQL and MySQL only when the view is created, and SQLite renames a view’s column along with the table’s.
• PostgreSQL and MySQL change a view with CREATE OR REPLACE VIEW; SQLite needs DROP VIEW and CREATE VIEW in one transaction, and it accepts a broken view that later blocks ALTER TABLE.
• Simple one-table views are updatable in PostgreSQL and MySQL; SQLite views are read-only until INSTEAD OF triggers say what a write means.
• WITH CHECK OPTION refuses writes that would leave the view; in an INSTEAD OF trigger, RAISE(ABORT, …) does the same job.
• Grant SELECT on a view and not on its table to limit what a user sees; SQLite has no privileges, so there a view only hides columns from code that uses it.