Views — Virtual Tables

A view is a named query stored in the database schema. When queried, the database executes the view's underlying query and presents the result as if it were a table. No data is physically stored in a regular view — it is purely a saved query definition. The view is transparent to the query engine: SELECT * FROM vw_store_revenue is exactly equivalent to embedding the view's full query inline.

Three primary use cases. First, abstraction and simplification: complex JOIN logic spanning multiple tables is defined once in a view and referenced by name in all downstream queries. When the schema changes, only the view definition needs updating — all queries that reference it automatically benefit. This is the most common production use of views in data engineering: a team of analysts queries clean, pre-joined views rather than raw base tables.

Second, security: views enforce column-level and row-level access control. A view can expose only a subset of columns (hiding salary, national ID, or full email) and only a subset of rows (filtering to rows belonging to a specific region or store). Granting SELECT on the view and revoking SELECT on the base table means users can only access what the view exposes. Third, API stability: when an application queries a database, the view is the stable API contract. The underlying table structure can change — columns renamed, tables split — without breaking the application, as long as the view is updated to maintain the same output column names and types.

A regular view stores only the query definition — no data is physically stored. Every time the view is queried, the database executes the underlying query against the current base table data and returns the result. The data is always current because it is computed on demand. The cost is that every query against the view pays the full computation cost of the underlying query.

A materialized view stores the result of the query physically on disk — it is a snapshot of the data at the time the view was last refreshed. Queries against a materialized view read the stored result, not the base tables — they are as fast as querying a regular indexed table. The cost is staleness: the stored result becomes out of date the moment the base tables change. Refresh must be triggered manually (REFRESH MATERIALIZED VIEW) or scheduled (via pg_cron or an external scheduler) to update the stored data.

Choose regular view when data freshness is required — financial transactions, real-time inventory, live order status. Choose materialized view when the underlying query is expensive (multi-table aggregation, complex analytics) and is queried frequently, and when slightly stale data is acceptable. A monthly revenue report that is refreshed nightly is a perfect materialized view candidate — the computation runs once at midnight, and the next day's 10,000 dashboard queries each read the stored result in milliseconds instead of recomputing a 5-second aggregation.

Yes, for simple views. A view is updatable when it meets these conditions: it selects from exactly one base table, it contains no GROUP BY or aggregate functions, no DISTINCT, no UNION, no LIMIT, no subqueries in the SELECT list, and no window functions. For updatable views, DML operations on the view are transparently passed through to the underlying base table — UPDATE vw_active_customers SET city = 'New York' WHERE customer_id = 5 executes as UPDATE customers SET city = 'New York' WHERE customer_id = 5.

WITH CHECK OPTION is an important addition to updatable views that have a WHERE filter. Without it, a user could INSERT a row through a view that does not satisfy the view's WHERE condition — the row would be inserted into the base table but immediately invisible through the view. WITH CHECK OPTION prevents this: any INSERT or UPDATE through the view must produce a row that satisfies the view's WHERE filter, or the operation fails with an error.

For complex views (those with JOINs, aggregations, or other features that make automatic DML mapping impossible), PostgreSQL supports INSTEAD OF triggers — trigger functions that intercept DML on the view and execute custom logic to update the appropriate base tables. This allows any view to appear updatable, but requires explicit implementation of the DML logic. In practice, most views that need to be updatable are deliberately kept simple to avoid the complexity of INSTEAD OF triggers.

Views implement two types of access control. Column-level security: a view can SELECT only a subset of columns from the base table, omitting sensitive columns entirely. CREATE VIEW vw_customers_public AS SELECT customer_id, first_name, city FROM customers — no email, no phone, no date of birth. When the support team is granted SELECT on vw_customers_public and has their SELECT permission on the base customers table revoked, they can query customer data but never see sensitive fields. The base table's sensitive columns are completely invisible to them.

Row-level security: a view can include a WHERE clause that limits which rows are visible. CREATE VIEW vw_my_region_orders AS SELECT * FROM orders WHERE region = current_setting('app.user_region') — each session that sets their region variable sees only their region's orders. This is how multi-tenant applications implement data isolation in PostgreSQL before row-level security policies (RLS) were available, and it remains a simple, portable approach.

The security model works by granting USAGE on the schema and SELECT on the view, while explicitly REVOKE-ing SELECT on the base tables. PostgreSQL also supports native Row Level Security (RLS) policies which are more flexible — they attach security rules directly to tables and enforce them automatically regardless of whether the user queries the table directly or through a view. For column masking, views remain the most portable approach across all SQL databases. For row filtering, both views and RLS policies are valid — RLS is preferred for complex multi-role scenarios because it cannot be bypassed by a direct table query as a view-only approach can be (if someone is accidentally granted direct table access).

The behaviour depends on the nature of the change. For additive changes (adding a new column to the base table), the view is unaffected — it continues to select its defined columns and the new column simply does not appear in the view unless the view definition is updated to include it. For destructive changes (removing or renaming a column that the view references), the view becomes invalid — querying it will fail with an error like "column does not exist" or "view definition is no longer valid."

PostgreSQL checks view validity lazily — the error appears when the view is queried, not when the underlying table is altered. Some databases (SQL Server, Oracle) track dependencies and can warn or prevent dropping a column that is referenced by a view. In PostgreSQL, you can check for stale views after a schema change by running SELECT definition FROM pg_views and parsing the definitions, or by attempting to query each view in a test environment.

This is exactly why views are valuable as an API stability layer. If you change the base table (split one large table into two, rename a column, add a column that should be computed differently) you update the view definition to maintain the same output structure. Downstream consumers — applications, reports, other views — never know the schema changed. The view absorbs the change. The migration process is: make the base table change, update the view definition (CREATE OR REPLACE VIEW or DROP + CREATE), verify the view still returns the expected output, and the downstream systems are unaffected. Without a view layer, every query that references the renamed column would need to be updated individually.

Cause: Another view (or trigger, or rule) references the view being dropped. PostgreSQL tracks dependencies between views and prevents dropping a view that would invalidate a dependent object. This happens when views are layered — dropping a base-layer view breaks views in higher layers.

Fix: Use DROP VIEW view_name CASCADE to automatically drop the dependent views as well. Before doing so, list what depends on the view: SELECT dependent_ns.nspname || '.' || dependent_view.relname FROM pg_depend JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid JOIN pg_class dependent_view ON pg_rewrite.ev_class = dependent_view.oid JOIN pg_class source ON pg_depend.refobjid = source.oid WHERE source.relname = 'your_view_name'. If the dependent views should be preserved, update their definitions to not reference the view being dropped before dropping it.

Cause: DML (UPDATE, INSERT, DELETE) is attempted on a view that contains GROUP BY, aggregate functions (SUM, COUNT, AVG), DISTINCT, or UNION. These features make it impossible for the database to determine which base table rows correspond to the view rows being modified — there is no direct row-level mapping.

Fix: For read-only analytical views, this is expected and correct — they are not meant to be updatable. Remove the DML attempt. If the view genuinely needs to support DML, simplify the view to contain only a single base table with a WHERE filter — no aggregation, no JOINs. For complex views that must support DML, implement INSTEAD OF triggers in PostgreSQL which intercept the DML and execute custom logic to update the appropriate base table rows.

Cause: This is a materialized view, not a regular view. Materialized views store a physical snapshot that is not automatically updated when base tables change. A regular view always reflects current base table data; a materialized view reflects data as of the last REFRESH.

Fix: Run REFRESH MATERIALIZED VIEW view_name to update the stored snapshot. For production: schedule REFRESH on a cron job (pg_cron extension in PostgreSQL) at an appropriate frequency — hourly for dashboards, nightly for monthly reports. For REFRESH without blocking concurrent reads, first create a unique index on the materialized view and use REFRESH MATERIALIZED VIEW CONCURRENTLY view_name. If data must always be current, convert to a regular view (DROP MATERIALIZED VIEW, CREATE VIEW with the same definition) — the performance cost of re-executing the query on each access may be acceptable.

Cause: The view's SELECT list contains two or more columns with the same name (either because two base tables both have a column called 'id', or because two computed expressions were not given aliases). Views require all output column names to be unique.

Fix: Add explicit AS aliases to disambiguate: SELECT a.id AS customer_id, b.id AS order_id FROM customers AS a JOIN orders AS b ON ... All columns that would produce duplicate names must be aliased. Also ensure computed expressions have aliases: SELECT COUNT(*) AS order_count rather than SELECT COUNT(*). Check for ambiguity when using SELECT * in views — if two joined tables share column names, SELECT * will produce duplicates. Explicitly list columns instead of using SELECT *.

Cause: The query planner is not optimally handling the view — either predicate pushdown is not working (filters on the view are not being pushed into the view's query), or the view nests multiple layers and the planner cannot see through all the abstraction. Also, a materialized view may not have the necessary indexes.

Fix: Use EXPLAIN ANALYZE on SELECT * FROM view_name WHERE condition to see the execution plan. If the filter is not pushed into the inner query, try rewriting as a CTE: WITH v AS NOT MATERIALIZED (view_definition) SELECT * FROM v WHERE condition — NOT MATERIALIZED forces the planner to inline the view definition and push predicates. For deeply nested view chains, consider flattening to a single view or a materialized view. For materialized views, add CREATE INDEX on the columns most commonly used in WHERE and JOIN conditions: CREATE INDEX idx_mv_col ON mv_name(column).

Design three views for FreshCart's reporting layer. Write the CREATE VIEW statements AND the verification SELECT queries for each. (1) vw_product_catalogue — a clean product view showing product_id, product_name (cleaned: trimmed, first letter capitalized and the rest lower-cased — SQLite has no per-word INITCAP, so this is sentence-case, not full multi-word Title Case), category, brand, unit_price, cost_price, margin_amount (unit_price - cost_price), margin_pct (rounded to 1dp), price_band ('Budget' <50, 'Standard' <150, 'Premium' <300, 'Luxury' otherwise), and in_stock. (2) vw_customer_segments — customer_id, full_name, city, loyalty_tier, lifetime_value (sum of delivered orders, 0 if none), order_count, last_order_date, and a segment: 'Champion' if lifetime_value > 1500, 'Loyal' if > 500, 'Promising' if order_count > 0, 'New/Inactive' otherwise. (3) vw_daily_sales — order_date, order_count, total_revenue, avg_order_value, unique_customers, and unique_products_sold — for delivered orders only. Then write one query that JOINs vw_product_catalogue to show the top 3 products by margin_pct in each price_band.