Does an ordinary view store an independent copy of its result rows?

No. It stores the defining query; materialized views are different.

Can a view be updated?

Some can, depending on the database and definition. It should not be assumed for every view.

Does excluding a column guarantee the user cannot access it elsewhere?

No. Other permissions and access paths must also be considered.

AI Glossary / Data engineering and architecture / Advanced

View

Definition

A view is a named database query that presents its result as a relation users can query much like a table. An ordinary, non-materialized view stores the query definition rather than a separately maintained copy of its result rows. It can select, combine or calculate information from underlying relations. Its meaning depends on that definition, and it should be distinguished from a materialized view that stores query results.

What it means

A view can give a recurring business question a stable name. For example, a learning catalog might define published_resources to show only resources currently marked published, with selected public-facing columns. Applications can query that named relation instead of repeating the same filter everywhere. The view is not a separate factual authority: it reflects the underlying data and its defined logic. If the publication status is wrong, the view can present the wrong selection consistently. An AI assistant should therefore understand both the view's purpose and the limits of its underlying information.

How it works

PostgreSQL documents that an ordinary view's defining query is evaluated when the view is referenced. Some simple views are updatable, while more complex ones are read-only by default; permissions and view options affect behavior. A designer specifies the output columns and selection logic, then tests the result and intended access. In an original publication view, selecting only the needed columns makes the interface clearer. Database visibility rules still govern which underlying changes a query sees. An ordinary view should not be described as a historical snapshot or as a guarantee of instant updates to an application's cached display.

Three practical examples

A learning website queries a named view of published resources to reuse the same publication filter across features.
A reporting team exposes a documented combination of resource and publisher information through one queryable relation.
An AI query assistant uses a curated analytical view whose column meanings are documented for the intended reporting task.

Think of it this way

Imagine looking through a window fitted with a frame that reveals a selected part of a noticeboard. The frame determines what is visible without creating another copy of every notice. Changes to the board can change what appears through the window. The analogy illustrates selection, but database views can also combine and calculate information, and transaction rules determine what a particular query can observe.

Worked example

Consider an original fictional Resource table with four rows: R1 published, R2 draft, R3 published and R4 retired. Each also has a title and an internal review note. Define an ordinary view that returns only ResourceID and Title for rows whose status equals published. The first query returns R1 and R3, so its row count is two. Now suppose R2 is changed to published and that change is committed. A later query in a context that can see the change returns R1, R2 and R3, giving three rows. R4 remains excluded. The view definition has not changed, and no separate result-refresh operation is needed for this ordinary view. The internal review-note column is absent from the view's output, but that alone does not prove a user lacks other routes to the underlying table. Access permissions must be evaluated separately. The example assumes unique resource rows and no joins that multiply them.

Illustrative case study

In a fictional AI learning portal, two features use different definitions of a published resource. One includes draft items accidentally. Reviewers create a shared view with the agreed selection rule and test the four-resource example. They update the features to use the documented interface and check the relevant database permissions. They also discover that a cached page can continue showing an earlier result, so they address application caching separately. The scenario illustrates centralizing query meaning while recognizing other system layers. It does not claim that creating a view automatically fixes every caller, hides all underlying data or accelerates every query.

Common misconceptions and mistakes

An ordinary view is not a frozen copy of its rows. A materialized view has different storage and refresh considerations. Another mistake is assuming all views are read-only or, conversely, that every derived result can be updated directly. Behavior depends on the definition and database. Naming a view also does not guarantee faster execution; the database still needs an appropriate plan. Finally, omitting a sensitive column from one view does not establish complete access control if the user can query another route. The intended security design must be checked as a whole.

Business use

For an AI reporting feature, use views to express stable, documented questions where that suits the database design. In the fictional catalog, define published precisely and identify the fields the feature requires. Test changes to the underlying data and to the view definition against dependent reports. Document whether a consumer expects current visible data, a fixed historical result or a separately refreshed representation. Review access behavior for the actual user roles. This helps teams share a consistent query interface while keeping data quality, permissions and application caching visible as separate responsibilities.

Check your understanding

How many rows are initially visible in the toy view?

Answer: Two, R1 and R3.

How many appear after the committed R2 change becomes visible to the later query?

Answer: Three.

Does the ordinary view require a separate materialized-result refresh?

Answer: No.

Why might a webpage still show two?

Answer: It may be displaying a cached result.

What assumption keeps the example's row count simple?

Answer: Each resource appears once and no join multiplies the rows.

Related terms

Materialized view
query
virtual relation
base table
database permissions
transaction isolation

Learn next

Define a fictional view using an explicit column list and one business filter. Predict its rows before and after an underlying update. Then identify how an ordinary view, a saved export and a materialized view differ in what they store and how their displayed information can change.

Materialized view

Sources

PostgreSQL documentation, CREATE VIEW: https://www.postgresql.org/docs/current/sql-createview.html The resource-status counts, noticeboard analogy and AI learning-portal scenario are original educational illustrations. Visibility is conditional on the query's transaction context; the example does not promise automatic refresh of application caches or complete security from column selection alone.

Previous: Vietoris - Rips filtration Next: View morphing