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?
answer
- one FROM entry, no grouping
- plain columns writable, computed read-only
- WHERE limits targets, not results
- bare CHECK OPTION means CASCADED
- SQLSTATE 44000
basics
~20 sA 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 sPostgreSQL 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 linesCREATE 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
Recall that simple PostgreSQL views over one table accept INSERT, UPDATE and DELETE directly, and that WITH CHECK OPTION exists.
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.
Show you know the CASCADED default, the 44000 error to handle in the application, and where check options are silently ignored.
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.