PostgreSQL View Can't Edit: Why Your View Is Read-Only and How to Fix It

If you cannot run UPDATE, INSERT, or DELETE against a PostgreSQL view, it is because your view is not simple enough to be automatically updatable. Postgres will let you write through a view only when it is a plain SELECT from one table with no joins, aggregates, DISTINCT, GROUP BY, or set operations. The moment you add a join or an aggregate, the view becomes read-only, because Postgres cannot figure out how to translate a change to the view back into a change to the underlying rows. The fix is either to simplify the view or to add an INSTEAD OF trigger that tells Postgres exactly how to apply the write.

The rules for an auto-updatable view
Why a join makes it read-only
Fix one: simplify or split the view
Fix two: an INSTEAD OF trigger
The older approach: rules, and why triggers win
How this fits the rest of the stack
FAQ

The rules for an auto-updatable view

Postgres makes a view automatically updatable only if it is simple. Specifically, the view’s SELECT must:

Reference exactly one table (or one updatable view) in its FROM.
Have no aggregates, no window functions, no DISTINCT.
Have no GROUP BY, HAVING, LIMIT, or OFFSET.
Have no UNION, INTERSECT, or EXCEPT.
Select columns as plain column references, not expressions, for any column you want to write to.

-- Automatically updatable: one table, plain columns
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE active = true;

UPDATE active_users SET email = '[email protected]' WHERE id = 5;  -- works

This view is a simple filtered window onto one table, so Postgres can map an UPDATE straight back to users. If your view looks like this and still cannot be edited, check permissions - but if it has a join or an aggregate, that is your answer, and no permission grant will change it.

Why a join makes it read-only

Add a join and the view stops being writable:

CREATE VIEW user_orders AS
SELECT u.name, o.total, o.created_at
FROM users u
JOIN orders o ON o.user_id = u.id;

UPDATE user_orders SET total = 99 WHERE ...;   -- error, not updatable

The reason is genuinely unsolvable in the general case. A row in this view is stitched from two tables. If you UPDATE name, that belongs to users; if you UPDATE total, that belongs to orders. If you try to INSERT a row, Postgres has no idea whether to create a user, an order, or both, or how to fill the columns the view does not expose.

There is no unambiguous answer, so Postgres refuses rather than guess. This is not a limitation to work around blindly - it is Postgres telling you that a write to this shape of view is genuinely ambiguous, and you need to specify what you mean. That is what INSTEAD OF triggers are for.

Fix one: simplify or split the view

Before reaching for triggers, ask whether the view needs to be that complex for the writes you actually do.

Often a view is a join for convenient reading, but you only ever write to one of the tables. In that case, write to the base table directly and keep the view for reads:

-- read through the convenient view
SELECT * FROM user_orders WHERE name = 'Sam';

-- write to the actual table
UPDATE orders SET total = 99 WHERE id = 42;

This is the simplest and most honest fix: read through the view, write to the base table. It avoids the ambiguity entirely because you are telling the database exactly which table to change.

If you genuinely need to write through the view - because callers should not know about the underlying tables - then you need a trigger, covered next. But check first whether write to the base table is an option, because it usually is and it is far less machinery.

Fix two: an INSTEAD OF trigger

When you must write through a complex view, an INSTEAD OF trigger intercepts the write and tells Postgres exactly what to do:

CREATE FUNCTION update_user_orders() RETURNS trigger AS $$
BEGIN
    UPDATE orders SET total = NEW.total
    WHERE id = OLD_order_id;   -- your logic to map the row back
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_orders_update
INSTEAD OF UPDATE ON user_orders
FOR EACH ROW EXECUTE FUNCTION update_user_orders();

An INSTEAD OF trigger fires in place of the write against the view, and inside it you write plain UPDATE/INSERT/DELETE statements against the real tables, deciding exactly how a change to the view maps back. You resolve the ambiguity by hand, which is the point - only you know the intended semantics.

This is the fully general solution: any view, no matter how complex, becomes writable once you supply INSTEAD OF triggers for the operations you need. The cost is that you own the mapping logic, and it has to stay correct as the schema evolves. Use it when writing through the view is a real requirement, not just a convenience.

The older approach: rules, and why triggers win

Postgres has an older mechanism, the rule system (CREATE RULE ... DO INSTEAD), that can also make views updatable. You will see it in older code and documentation.

Prefer INSTEAD OF triggers. Rules rewrite the query at the planner level, which has surprising interactions - they can execute the underlying query multiple times, behave unexpectedly with RETURNING, and are notoriously hard to reason about. The Postgres community has largely moved to triggers for exactly these reasons.

-- avoid this style for new code
CREATE RULE ... AS ON UPDATE TO my_view DO INSTEAD ...;

If you are maintaining a codebase that uses rules for updatable views and they work, there is no urgency to rewrite them. But for anything new, INSTEAD OF triggers are clearer, more predictable, and the recommended path. When a guide shows you rules, treat it as a sign the guide is old, and reach for a trigger instead.

How this fits the rest of the stack

Deciding whether to write through a view or to the base table is exactly the kind of schema-design call that is cheap to reason about early and expensive to retrofit. When Postgres is a managed database behind your app, keeping the read and write paths clear is what keeps the data layer predictable as the product grows. The RunxBuild hosting calculator lays out the service, database, storage, and bandwidth as separate line items, and the RunxBuild dashboard is where the team watches deploys, logs, and restarts as they happen.

Useful related references:

FAQ

Why can’t I update a PostgreSQL view?

Because the view is not simple enough to be automatically updatable. Postgres allows writes only through a view that selects from a single table with no joins, aggregates, DISTINCT, GROUP BY, or set operations. Add a join or aggregate and the view becomes read-only, since Postgres cannot map the write back unambiguously.

What makes a PostgreSQL view updatable?

The view must select from exactly one table, use plain column references for writable columns, and avoid aggregates, window functions, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET, and UNION/INTERSECT/EXCEPT. A simple filtered SELECT from one table is automatically updatable.

How do I make a view with a join updatable in Postgres?

Add an INSTEAD OF trigger. The trigger fires in place of the write and runs your own UPDATE, INSERT, or DELETE against the underlying tables, so you define exactly how a change to the view maps back. Alternatively, read through the view and write directly to the base table.

Should I use rules or triggers to make a view updatable?

Use INSTEAD OF triggers. The older rule system rewrites queries at the planner level and has surprising behaviour - it can run the underlying query multiple times and interacts badly with RETURNING. Triggers are clearer and are the recommended approach for new code.

Can I write to the base table instead of the view?

Usually yes, and it is the simplest fix. Keep the complex view for convenient reads and run your UPDATE, INSERT, and DELETE statements directly against the underlying table. This avoids the ambiguity of writing through a joined view entirely.