skip to content

In PostgreSQL, what makes a view automatically updatable, and what stops an UPDATE through a region-filtered view from moving a row out of it?

level: middleimportance: should knowfreq 45%

answer

  1. one FROM entry, no grouping
  2. plain columns writable, computed read-only
  3. WHERE limits targets, not results
  4. bare CHECK OPTION means CASCADED
  5. SQLSTATE 44000

basics

~20 s

A PostgreSQL view is auto-updatable when it reads one table or updatable view without WITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET, set operations, aggregates, window or set-returning functions. WITH CHECK OPTION rejects rows the view could not see.

solid answer

~40 s

PostgreSQL rewrites `INSERT`, `UPDATE`, `DELETE` and, since 17, `MERGE` on a view into the same statement on the base table when the view has exactly one `FROM` entry, a table or another updatable view, and no top-level `WITH`, `DISTINCT`, `GROUP BY`, `HAVING`, `LIMIT`, `OFFSET` or set operation, and no aggregates, window or set-returning functions in its select list. Plain column references are writable; computed columns are read-only. The view's `WHERE` limits which rows an `UPDATE` can target, but not the new values, so `SET region = 'US'` succeeds and the row disappears. `WITH CHECK OPTION` stops that with `new row violates check option for view` (SQLSTATE `44000`). Written bare it means `CASCADED`, which also checks underlying views; `LOCAL` checks only this view's own condition.

code

sql · 17 lines
sql
CREATE VIEW support.eu_customers AS
SELECT customer_id, full_name, email, region,
       upper(email) AS email_upper
FROM   crm.customers
WHERE  region = 'EU'
WITH CHECK OPTION;                     -- bare form = CASCADED

UPDATE support.eu_customers SET email = '[email protected]'
WHERE  customer_id = 42;               -- rewritten onto crm.customers

UPDATE support.eu_customers SET region = 'US'
WHERE  customer_id = 42;
-- ERROR:  new row violates check option for view "eu_customers"

UPDATE support.eu_customers SET email_upper = 'X'
WHERE  customer_id = 42;
-- ERROR:  cannot update column "email_upper" of view "eu_customers"

go deeper

for a junior

Recall that simple PostgreSQL views over one table accept INSERT, UPDATE and DELETE directly, and that WITH CHECK OPTION exists.

for a middle

Explain the auto-updatable conditions, why computed columns are read-only, and why an UPDATE can push a row out of the view without a check option.

for a senior

Show you know the CASCADED default, the 44000 error to handle in the application, and where check options are silently ignored.

for a principal

Judge when a writable view is a sound access boundary for a team and when the logic belongs in a function or trigger instead.

## When PostgreSQL rewrites a write on a view A plain PostgreSQL view has no storage; it is a stored query. For a simple view, though, the rewriter turns `INSERT`, `UPDATE`, `DELETE` and, from 17, `MERGE` on the view into the matching statement on the one base relation underneath. No trigger or rule is needed. The documented conditions for a view to be **automatically updatable** are: - exactly one entry in `FROM`, which is a table or another updatable view; - no `WITH`, `DISTINCT`, `GROUP BY`, `HAVING`, `LIMIT` or `OFFSET` at the top level; - no `UNION`, `INTERSECT` or `EXCEPT` at the top level; - no aggregates, window functions or set-returning functions in the select list. The rewriter also refuses a view whose `FROM` entry carries `TABLESAMPLE`. When a write hits a view that fails the test, PostgreSQL names the reason in the error detail and suggests the alternative: ```console ERROR: cannot insert into view "region_totals" DETAIL: Views containing GROUP BY are not automatically updatable. HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule. ``` `INSTEAD OF` triggers for such views are the updatable-views topic's subject. ## Column by column An auto-updatable view may mix writable and read-only columns: | Column | Behaviour on a write through the view | |---|---| | `email`, a plain reference to a base column | Writable | | `upper(email) AS email_upper` | Read-only: `cannot update column "email_upper" of view ...` | | Base column not listed in the view | Gets the base table's default on `INSERT` | `ALTER VIEW ... ALTER COLUMN ... SET DEFAULT` gives the view its own default for inserts made through it. `INSERT ... ON CONFLICT DO UPDATE` through an auto-updatable view is fully supported. ## Rows that escape the view The view's `WHERE` clause restricts which base rows `UPDATE`, `DELETE` and `MERGE` can **reach**. It does not restrict what the new row looks like. Through `support.eu_customers` (`WHERE region = 'EU'`): 1. `UPDATE ... SET email = ...` on an EU customer works. 2. `UPDATE ... SET region = 'US'` also works, and the row vanishes from the view. 3. `INSERT` of a row with `region = 'US'` succeeds and is invisible through the view. For a support team allowed to edit only EU customers, cases 2 and 3 are holes. ## WITH CHECK OPTION in PostgreSQL `WITH CHECK OPTION` makes `INSERT`, `UPDATE` and `MERGE` verify that each new row is still visible through the view. A failure raises `new row violates check option for view "eu_customers"` with SQLSTATE `44000` (`with_check_option_violation`) and a `Failing row contains (...)` detail. | Written as | Effective mode | Checks | |---|---|---| | `WITH CHECK OPTION` | `CASCADED` | This view and every underlying view | | `WITH CASCADED CHECK OPTION` | `CASCADED` | This view and every underlying view | | `WITH LOCAL CHECK OPTION` | `LOCAL` | This view's own conditions, plus underlying views that declare their own check option | | `WITH (check_option = local)` | `LOCAL` | Same as above, as a view option | The engine-neutral meaning of the two modes is published elsewhere; the PostgreSQL facts to remember are the `CASCADED` default, the `check_option` view option that `ALTER VIEW ... SET` can change later, and the `44000` error an application should map to a validation failure. ## Edges worth knowing - The check option is supported only on auto-updatable views. A view written through `INSTEAD OF` triggers ignores check options defined on it, and a `CASCADED` check does not reach down into it. - If the view or a base relation has an `INSTEAD` rule that rewrites the write, all check options are ignored; `MERGE` is refused on relations with rules. - Privileges: the writer needs `UPDATE` on the view; by default the view owner needs it on the base table, or the writer does if the view has `security_invoker = true`. - To check from SQL, `information_schema.views` exposes `is_updatable`, `is_insertable_into` and `check_option` (`CASCADED`, `LOCAL` or `NONE`), and `pg_relation_is_updatable()` and `pg_column_is_updatable()` answer per relation and per column.

  • How can you tell from SQL whether a PostgreSQL view is auto-updatable and which check option it carries?
    Query `information_schema.views`: `is_updatable` and `is_insertable_into` say whether writes are accepted, and `check_option` shows `CASCADED`, `LOCAL` or `NONE`. For a single relation or column, `pg_relation_is_updatable()` and `pg_column_is_updatable()` give the same answer as functions.
  • A PostgreSQL view has WITH CHECK OPTION but is written through an INSTEAD OF trigger; is the check enforced?
    No. The check option is supported only on auto-updatable views, and check options defined directly on a trigger-updatable view are ignored. A cascaded check from a view stacked on top also stops at it, so the trigger function must validate the new row itself.

  • Any view over a single table is updatable, even with GROUP BY or DISTINCT.
  • A view's WHERE clause automatically blocks updates that move rows out of it.
  • A bare WITH CHECK OPTION in PostgreSQL means LOCAL.
  • One computed column makes the whole view read-only.
  • WITH CHECK OPTION also validates rows written through an INSTEAD OF trigger.

Assumes PostgreSQL 18; MERGE into an auto-updatable view needs 17 or later, while INSERT, UPDATE and DELETE through such views long predate it.