Advanced Views

By the end of this lesson you'll know exactly which views you can write through and which you can't — and how to fix the ones you can't. You'll build updatable views guarded by WITH CHECK OPTION , lock data down with security views, and make a complex join view writable with an INSTEAD OF trigger.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Every example uses this products table — plus a small orders table for the join examples. Knowing the data is half of writing good SQL.

And a tiny orders table — each row links to a product by product_id :

A view is a saved SELECT that behaves like a virtual table — it stores no data and re-runs its query every time you read it. The surprise for many people: you can also write through a view, and the change lands in the real table underneath. PostgreSQL allows this automatically when the view is simple enough to map each result row back to exactly one base-table row.

An updatable view is a window onto a table. You can reach through the window to move things on the shelf behind it — but only if the window shows one clear shelf. A view that summarises many shelves (an aggregate) is more like a security camera: great for looking, impossible to reach through.

A view is auto-updatable only when it: reads exactly one table in FROM ; has no GROUP BY , HAVING , DISTINCT , LIMIT , OFFSET , UNION / INTERSECT / EXCEPT ; and uses no aggregate or window functions and no sub-query in the SELECT list. Break any of those and the view turns read-only.

Because v_in_stock reads one table with no grouping, the database lets you write through it. Each statement below changes the real products row:

A filtered view has a sharp edge. v_in_stock only shows rows where stock 0 , but by default you can still INSERT a row with stock = 0 through it. The insert succeeds, then the new row instantly disappears from the view because it fails the filter — a baffling "I just added it, where did it go?" bug.

WITH CHECK OPTION closes that gap. It tells the database to apply the view's WHERE to writes as well as reads, rejecting any INSERT or UPDATE that would produce a row the view can't see.

Fill in the blanks to create an updatable view of budget products ( price 20 ) that refuses to let a row be priced out of itself. The expected behaviour is in the comments so you can check yourself.

The moment a view summarises data, writing through it stops making sense. If v_category_stats shows one row per category with an average price, what would it even mean to UPDATE that average? The database can't split one summary row back into the individual rows it came from, so it refuses.

The same logic applies to most join views : a single result row stitches together columns from several tables, and an ambiguous write can't be safely undone. PostgreSQL makes a simple join view read-only by default — you'll make it writable in Section 5 with a trigger.

A view is a clean way to expose part of a table. You lock down the base table, then grant access only to a view that selects the safe columns and rows. Callers can query the view but never touch the data it hides — costs, supplier IDs, other people's records.

Two safety switches matter. security_invoker = true (PostgreSQL 15+) runs the view with the caller's privileges, so any row-level security on the base table still applies to them; without it a view runs as its owner and can hand back rows the caller was never permitted to see. The older security-barrier view ( WITH (security_barrier = true) ) forces the view's own WHERE to run before any function the caller injects, so a cleverly-crafted predicate can't peek at filtered-out rows.

Build a view that exposes only product_name and price (hiding stock and cost) and only the Electronics rows, running with the caller's own permissions. Fill in the three blanks.

What if you genuinely need to write through a join view like v_order_lines ? You take control of the write yourself with an INSTEAD OF trigger . The name is literal: instead of the database's default (which is "error, this view is read-only"), it runs your function, and your function writes to the real tables however you decide.

Below, an INSTEAD OF INSERT trigger reads the incoming row, looks up the product's id from the name the view exposed, and inserts a proper row into orders . From the caller's side it looks like an ordinary insert into the view.

Everything above is a plain view : zero stored data, always live, re-computed on every read. That's perfect when freshness matters and the query is cheap. But when a view's query is expensive (big joins, heavy aggregates) and you read it far more often than the data changes, re-running it every time is wasteful.

A materialized view trades freshness for speed: it runs the query once and stores the result like a cached table, which you refresh with REFRESH MATERIALIZED VIEW . Reads become as fast as reading a table; the cost is that the data is only as current as the last refresh — and materialized views are not updatable.

Q: How do I know if a view is updatable before I try?

Check the rules: one table in FROM , and no GROUP BY , HAVING , DISTINCT , LIMIT / OFFSET , set operations, aggregates, or window functions. In PostgreSQL you can also query information_schema.views — the is_updatable column says yes or no.

No. It only affects INSERT and UPDATE , adding a check that the resulting row still matches the view. Reads are unaffected, so there's almost never a reason to leave it off a filtered updatable view.

Q: Is hiding a column in a view enough to keep it secret?

Only if the caller can't read the base table. A view doesn't remove anyone's existing table privileges — you must REVOKE on the table and GRANT on the view. For row-level hiding, add security_invoker or security_barrier .

Q: View or materialized view for a slow dashboard query?

If the numbers can be a few minutes stale and you read them constantly, a materialized view is far faster — query once, store, REFRESH on a schedule. If you need always-live data or want to write through it, keep a plain view.

Put it together — a brief, a blank canvas, and the expected behaviour in the comments. Write it, then copy it into a PostgreSQL playground to confirm.

Practice quiz

What does a plain (non-materialized) view store?

  • A cached copy of the rows
  • Only the column names
  • No data of its own — it re-runs its query each time
  • A snapshot from creation time

Answer: No data of its own — it re-runs its query each time. A view stores no data; it is a saved SELECT that re-runs every time you read it.

Which of these makes a view NOT auto-updatable?

  • A GROUP BY or aggregate function
  • Selecting a few columns from one table
  • A WHERE filter
  • A column alias

Answer: A GROUP BY or aggregate function. GROUP BY, aggregates, DISTINCT, joins, etc. break auto-updatability; one plain table is required.

What does WITH CHECK OPTION do?

  • Speeds up reads
  • Makes the view read-only
  • Caches the result
  • Applies the view's WHERE to writes, rejecting rows that would fall outside it

Answer: Applies the view's WHERE to writes, rejecting rows that would fall outside it. It enforces the view's filter on INSERT/UPDATE so a row can't be written out of the view.

Without WITH CHECK OPTION, what happens if you INSERT a row through a filtered view that fails the filter?

  • The insert errors
  • The row is created but instantly disappears from the view
  • The view is dropped
  • Nothing is inserted

Answer: The row is created but instantly disappears from the view. The row is written to the base table but vanishes from the view — the 'disappearing insert' bug.

Why is a view containing GROUP BY read-only?

  • A summary row can't be mapped back to the individual base rows
  • GROUP BY is slow
  • Aggregates are not real columns
  • It uses too much memory

Answer: A summary row can't be mapped back to the individual base rows. The database can't reverse one summary row into the rows it came from, so writes are refused.

What does WITH (security_invoker = true) do (PostgreSQL 15+)?

  • Encrypts the view
  • Hides all columns
  • Runs the view with the caller's privileges instead of the owner's
  • Makes the view updatable

Answer: Runs the view with the caller's privileges instead of the owner's. security_invoker runs the view as the caller, so the caller's row-level security still applies.

Is hiding a column by omitting it from a view's SELECT enough to secure it?

  • Yes, always
  • No — if the caller still has SELECT on the base table they can bypass the view
  • Yes, if the view is materialized
  • Only for numeric columns

Answer: No — if the caller still has SELECT on the base table they can bypass the view. Real security needs REVOKE on the table plus GRANT on the view; omitting a column alone is not enough.

What is an INSTEAD OF trigger used for?

  • Speeding up reads
  • Refreshing materialized views
  • Enforcing WITH CHECK OPTION
  • Making an otherwise read-only (join) view writable with custom logic

Answer: Making an otherwise read-only (join) view writable with custom logic. An INSTEAD OF trigger replaces the default write with your function, routing it to the real tables.

How does a materialized view differ from a plain view?

  • It is always read-live
  • It stores the query result like a cache and must be refreshed
  • It can never be queried
  • It only works on one table

Answer: It stores the query result like a cache and must be refreshed. A materialized view stores the result for speed; data is only as fresh as the last REFRESH.

When is a materialized view the better choice over a plain view?

  • When you need always-live data
  • When you need to write through it
  • For an expensive, slow-changing report you read constantly
  • When the query is trivial

Answer: For an expensive, slow-changing report you read constantly. Materialized views trade freshness for speed — ideal for expensive reports read far more than they change.