Skip to main content

Updatable vs Read-Only Views

Learning Focus

Use this lesson to decide whether a PostgreSQL view can support INSERT/UPDATE/DELETE, enforce view predicates with WITH CHECK OPTION, and recognize when a view is inherently read-only.

Concept Overview​

Not all views behave the same for writes.

In PostgreSQL:

  • Many simple views are automatically updatable.
  • Views that include joins, aggregations, set operations, or other non-1:1 mappings are typically read-only unless you add INSTEAD OF triggers.

Why is it important?​

  • Correctness: avoid thinking your writes succeeded when they are rejected
  • API design: decide whether a view is a read model or a write interface
  • Safety: WITH CHECK OPTION prevents inserting/updating rows that don't match the view
  • Maintainability: write rules belong either in base tables or explicit triggers, not implicit assumptions

Where does it fit?​

This shows up when you:

  • expose views to applications instead of base tables
  • use views as a compatibility layer during migrations
  • build multi-tenant row-restricted interfaces

Syntax & Rules​

Core Syntax​

Create a simple (often updatable) view:

CREATE VIEW v_active_users AS
SELECT user_id, email, status
FROM users
WHERE status = 'active';

Enforce the view predicate on writes:

CREATE VIEW v_active_users AS
SELECT user_id, email, status
FROM users
WHERE status = 'active'
WITH CHECK OPTION;

Automatic Updatability (Rule of Thumb)​

A view is commonly automatically updatable when:

  • it selects from a single base table (or another updatable view)
  • it does not use DISTINCT, GROUP BY, aggregates, window functions
  • it does not use set operations (UNION, INTERSECT, EXCEPT)
  • it does not contain joins

If a view isn't automatically updatable, PostgreSQL typically returns an error and suggests INSTEAD OF triggers.

Available Options / Parameters​

FeatureExampleMeaning / Notes
WITH CHECK OPTION... WITH CHECK OPTIONrejects writes that would make the row invisible to the view
LOCAL / CASCADEDWITH LOCAL CHECK OPTIONhow check option behaves with nested views
INSTEAD OF triggersCREATE TRIGGER ... INSTEAD OF INSERTimplement custom write logic for complex views

Key Rules and Considerations​

  • Even if a view is updatable, you can still make it effectively read-only via privileges (grant only SELECT).
  • WITH CHECK OPTION is critical when using views to enforce row restrictions.
  • If the view includes derived columns (expressions), those columns are not directly writable.

Step-by-Step Examples​

Use this setup for the examples:

DROP VIEW IF EXISTS v_users_active;
DROP VIEW IF EXISTS v_customer_orders;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS customers;

CREATE TABLE users (
  user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  status text NOT NULL
);

INSERT INTO users (email, status) VALUES
  ('a@example.com', 'active'),
  ('b@example.com', 'inactive');

CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(customer_id),
  amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);

INSERT INTO customers (email) VALUES ('c@example.com');
INSERT INTO orders (customer_id, amount) VALUES (1, 10.00);

Example 1: An Automatically Updatable View (Beginner)​

CREATE VIEW v_users_active AS
SELECT user_id, email, status
FROM users
WHERE status = 'active';

UPDATE v_users_active
SET email = 'a2@example.com'
WHERE user_id = 1;

SELECT user_id, email, status
FROM users
ORDER BY user_id;

Expected output:

CREATE VIEW
UPDATE 1
 user_id |     email       |  status
---------+------------------+----------
 1       | a2@example.com   | active
 2       | b@example.com    | inactive
(2 rows)

Explanation:

  • The view maps 1:1 to rows in users, so PostgreSQL can update the base table.

Example 2: A Join View is Read-Only by Default (Intermediate)​

CREATE VIEW v_customer_orders AS
SELECT c.customer_id, c.email, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

UPDATE v_customer_orders
SET amount = 99.00
WHERE order_id = 1;

Expected outcome:

CREATE VIEW
ERROR:  cannot update view "v_customer_orders"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

Explanation:

  • The join means a view row does not map cleanly to exactly one underlying table row.

Example 3: Enforce the View Predicate With WITH CHECK OPTION (Advanced)​

DROP VIEW IF EXISTS v_users_active;

CREATE VIEW v_users_active AS
SELECT user_id, email, status
FROM users
WHERE status = 'active'
WITH CHECK OPTION;

INSERT INTO v_users_active (email, status)
VALUES ('x@example.com', 'inactive');

Expected outcome:

DROP VIEW
CREATE VIEW
ERROR:  new row violates check option for view "v_users_active"
DETAIL:  Failing row contains (...).

Fix:

INSERT INTO v_users_active (email, status)
VALUES ('x@example.com', 'active');

SELECT user_id, email, status
FROM v_users_active
ORDER BY user_id;

Expected output:

INSERT 0 1
 user_id |     email       | status
---------+------------------+--------
 1       | a2@example.com   | active
 3       | x@example.com    | active
(2 rows)

Explanation:

  • Without WITH CHECK OPTION, an insert/update could create a row that immediately "disappears" from the view.

Practical Use Cases​

1) Active-Only Interfaces​

Context: present only active rows to applications.

CREATE VIEW v_active_users AS
SELECT * FROM users WHERE status = 'active'
WITH CHECK OPTION;

2) Multi-Tenant Row Restrictions​

Context: build per-tenant views (often paired with permissions).

CREATE VIEW v_tenant_42_orders AS
SELECT * FROM orders WHERE tenant_id = 42
WITH CHECK OPTION;

3) Read Models for Reporting​

Context: join + aggregate views are often read-only by design.

CREATE VIEW v_daily_revenue AS
SELECT date_trunc('day', created_at) AS day, SUM(amount)
FROM orders
GROUP BY 1;

Common Mistakes & Troubleshooting​

Mistake 1: Expecting Join Views to Be Updatable​

What happens: PostgreSQL throws an error and suggests INSTEAD OF triggers.

Fix: write to base tables, or implement an explicit INSTEAD OF trigger when appropriate.

Mistake 2: Forgetting WITH CHECK OPTION​

What happens: inserts/updates through the view can produce rows that do not satisfy the view predicate.

Fix: add WITH CHECK OPTION when the view is used as a policy boundary.

Mistake 3: Granting Writes to Views Without Understanding Side Effects​

What happens: applications start depending on view write semantics, making migrations harder.

Fix: treat writable views as an API contract, document it, and test it.

Debugging tips:

  1. If an UPDATE fails, check whether the view is automatically updatable (single-table, no joins/aggregates).
  2. Inspect view definition with \d+ view_name or pg_get_viewdef.
  3. If you need write support, decide between base-table writes vs INSTEAD OF triggers.
  4. For policy views, test that writes violating the predicate are rejected.

Best Practices​

  • ✅ Use updatable views only when they are clearly 1:1 with a base table; ❌ treat complex reporting views as writable.
  • ✅ Add WITH CHECK OPTION for policy views; ❌ allow writes that produce invisible rows.
  • ✅ Prefer writing to base tables for complex cases; ❌ hide business writes in implicit view behavior.
  • ✅ Control privileges explicitly; ❌ grant broad table access and hope the view is used.
  • ✅ Keep view write contracts stable and tested; ❌ change writable view behavior without coordination.

Hands-On Practice​

Use this setup for the exercises:

DROP VIEW IF EXISTS practice_v_active;
DROP TABLE IF EXISTS practice_users;

CREATE TABLE practice_users (
  user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  status text NOT NULL
);

INSERT INTO practice_users (email, status) VALUES
  ('p1@example.com', 'active'),
  ('p2@example.com', 'inactive');

Exercise 1 (Easy): Create an updatable view​

Task: Create a view practice_v_active that selects only active users.

-- Your SQL here

Solution:

CREATE VIEW practice_v_active AS
SELECT user_id, email, status
FROM practice_users
WHERE status = 'active';

Exercise 2 (Medium): Update through the view​

Task: Update the active user's email through the view.

-- Your SQL here

Solution:

UPDATE practice_v_active
SET email = 'p1_new@example.com'
WHERE user_id = 1;

Exercise 3 (Advanced): Add CHECK OPTION​

Task: Recreate the view with WITH CHECK OPTION and attempt to insert an inactive row through the view (it should fail).

-- Your SQL here

Solution:

DROP VIEW practice_v_active;

CREATE VIEW practice_v_active AS
SELECT user_id, email, status
FROM practice_users
WHERE status = 'active'
WITH CHECK OPTION;

INSERT INTO practice_v_active (email, status)
VALUES ('bad@example.com', 'inactive');

Connection to Other Concepts​

ConceptWhy it matters
Privilegesyou can make a view read-only by granting only SELECT
TriggersINSTEAD OF triggers enable custom view write behavior
Constraintsbase table constraints still apply to writes through views
Joins and aggregatesoften make views read-only by default
Migrationswritable views are contracts that must be managed carefully

Visual Learning Diagram​

flowchart TD
  A[View Definition] --> B{Single-table\nno joins/aggregates?}
  B -->|Yes| C[Automatically updatable]
  B -->|No| D[Read-only by default]
  D --> E[INSTEAD OF trigger\n(if write needed)]
  C --> F[WITH CHECK OPTION\n(optional safety)]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F allNodes
  class C highlight

Common Pitfalls​

PitfallConsequencePrevention
Join view writeserrors at runtimewrite to base tables or add INSTEAD OF trigger
Missing WITH CHECK OPTIONrows disappear from view after insert/updateadd check option for policy views
Treating writable views as accidental APImigration paindocument and test view write contracts
Assuming view bypasses constraintsinserts fail unexpectedlyremember base constraints still apply
Over-granting base table accessview policy ineffectivegrant on views, restrict base tables

Quick Reference​

CREATE VIEW v AS SELECT ...
CREATE VIEW v AS SELECT ... WITH CHECK OPTION
UPDATE v SET ... WHERE ...
ERROR: cannot update view (join/aggregate) -> use INSTEAD OF triggers
GRANT SELECT ON v TO role

What's Next​