SQL 66 🛢️ Creating and Managing Views
A view is a named query that behaves like a table. It does not store data — it stores the query — and when it is referenced, the database executes the underlying query and returns the result. The view is the abstraction that hides the complexity of the joins, the aggregations, and the filtering behind a simple name. A query that joins five tables and computes three aggregates becomes a view called order_summary, and the applications that need the summary select from the view instead of repeating the join. The view is the encapsulation, and the underlying tables remain the source of truth.
The distinction between a view and a table is the distinction between the query and the data. A table stores the rows, and a view computes them. The view is not materialized unless the database supports the materialized views, and even then, the materialized view is a cached copy that must be refreshed. A regular view is always current: it reflects the latest data in the underlying tables. The trade-off is the performance: a complex view re-executes the query on every reference, and the performance depends on the query’s cost.
The views have several uses. They simplify the complex queries by giving them the names. They provide the security by exposing only the columns and the rows that the user is allowed to see. They provide the abstraction by decoupling the applications from the underlying schema, so the schema can change without breaking the applications. They provide the compatibility by presenting the data in the shape the legacy applications expect, even when the underlying tables have changed. The LFCS and the SQL exams cover the views as part of the “Use SQL to query and manipulate data” objective.
This chapter covers three areas. First, why views exist — the simplification, the security, and the abstraction. Second, how to create and manage the views — the CREATE VIEW statement, the CREATE OR REPLACE VIEW, the ALTER VIEW, the DROP VIEW, and the updatable views. Third, the advanced patterns — the views over the views, the views with the parameters (the functions), the materialized views, and the security with the views. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the view resolution.
Key point: A view is a named query that behaves like a table. It does not store data — it stores the query — and the database executes the query when the view is referenced. A view can be used anywhere a table can be used in a SELECT. An updatable view allows the INSERT, the UPDATE, and the DELETE to be performed through the view, subject to the constraints. The CREATE OR REPLACE VIEW replaces the view’s definition, and the DROP VIEW removes it.
Why views exist
The simplification problem. A query that joins five tables and computes the aggregates is long and complex. The applications that need the result would have to repeat the query, and the repetition would lead to the inconsistencies. The view encapsulates the query behind a name. The applications select from the view, and the complexity is in one place. When the query changes, the view is updated, and the applications are unaffected.
The security problem. A table contains the sensitive columns — the salary, the social security number, the password hash — that not every user should see. The view can expose only the columns the user is allowed to see. The CREATE VIEW employee_public AS SELECT id, name, department FROM employees exposes the id, the name, and the department, and hides the salary and the social security number. The user is granted the SELECT on the view, and the access to the underlying table is denied. The view is the security boundary, and the grant is the mechanism.
The abstraction problem. The applications depend on the schema. When the schema changes — a column is renamed, a table is split, a new column is added — the applications break. The view decouples the applications from the schema. The view presents the data in the shape the applications expect, and the underlying schema can change as long as the view is updated. The CREATE OR REPLACE VIEW updates the view without dropping it, and the grants on the view are preserved.
The compatibility problem. The legacy applications expect the data in the old shape. The new schema has the data in the new shape. The view is the compatibility layer: it presents the old shape to the legacy applications and the new shape to the new applications. The view is the bridge between the two.
The computed-column problem. A view can include the computed columns that the applications need. The CREATE VIEW order_totals AS SELECT order_id, SUM(quantity * price) AS total FROM order_items GROUP BY order_id computes the total on the fly. The applications select the total from the view, and the computation is in one place.
The trade-off. A view is not a table. It does not store the data, and it does not have the indexes of its own. The complex view re-executes the query on every reference, and the performance depends on the underlying query and the indexes on the base tables. The materialized view stores the data, but the data is stale until the refresh. The trade-off is between the freshness and the performance, and the choice depends on the workload.
a. Creating a view
The CREATE VIEW statement defines the view. The view’s name follows, and the query is in the AS clause.
CREATE VIEW employee_public AS
SELECT id, name, department
FROM employees;The view is created, and the SELECT * FROM employee_public returns the id, the name, and the department. The view does not store the data; it stores the query. When the view is referenced, the database executes the query.
The column names can be specified explicitly:
CREATE VIEW employee_public (id, full_name, dept) AS
SELECT id, name, department
FROM employees;The view’s columns are the id, the full_name, and the dept, and the mapping is positional. The explicit names are the way to rename the columns in the view.
The OR REPLACE clause replaces the existing view:
CREATE OR REPLACE VIEW employee_public AS
SELECT id, name, department, hire_date
FROM employees;The CREATE OR REPLACE is the idempotent form. It creates the view if it does not exist, and it replaces it if it does. The grants on the view are preserved, which is the advantage over the DROP and the CREATE.
The IF NOT EXISTS clause prevents the error if the view exists:
CREATE VIEW IF NOT EXISTS employee_public AS
SELECT id, name, department
FROM employees;The IF NOT EXISTS is the SQLite and the MySQL syntax. The PostgreSQL uses the CREATE OR REPLACE.
The temporary view. The CREATE TEMPORARY VIEW or the CREATE TEMP VIEW creates a view that exists only for the session. The view is dropped when the session ends.
CREATE TEMP VIEW temp_summary AS
SELECT department, COUNT(*) AS count
FROM employees
GROUP BY department;The view with the joins. The view can join the multiple tables:
CREATE VIEW order_details AS
SELECT
o.id AS order_id,
o.order_date,
c.name AS customer_name,
p.name AS product_name,
oi.quantity,
oi.price,
oi.quantity * oi.price AS line_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;The view hides the four joins behind a single name. The applications select from the order_details, and the joins are in one place.
The view with the aggregates. The view can compute the aggregates:
CREATE VIEW customer_totals AS
SELECT
c.id AS customer_id,
c.name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS lifetime_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;The view computes the order count and the lifetime total for each customer. The LEFT JOIN includes the customers with no orders, and the COALESCE handles the NULL.
b. Managing the views
The ALTER VIEW. The ALTER VIEW changes the view’s properties, not its query. The ALTER VIEW ... RENAME TO renames the view, and the ALTER VIEW ... OWNER TO changes the owner.
ALTER VIEW employee_public RENAME TO employee_directory;
ALTER VIEW employee_public OWNER TO admin;The ALTER VIEW does not change the query. The query is changed with the CREATE OR REPLACE VIEW.
The DROP VIEW. The DROP VIEW removes the view. The IF EXISTS prevents the error if the view does not exist, and the CASCADE drops the dependent views.
DROP VIEW employee_public;
DROP VIEW IF EXISTS employee_public;
DROP VIEW employee_public CASCADE;The CASCADE drops the views that depend on the view being dropped. Without the CASCADE, the drop fails if a dependent view exists.
The pg_views and the information_schema. The views are listed in the pg_views (the PostgreSQL) or the information_schema.views (the standard).
-- The PostgreSQL
SELECT schemaname, viewname, viewowner
FROM pg_views
WHERE schemaname = 'public';
-- The standard
SELECT table_name
FROM information_schema.views
WHERE table_schema = 'public';
-- The view definition
SELECT pg_get_viewdef('employee_public', true);The pg_get_viewdef returns the view’s query, and the \d+ view_name in the psql shows the columns and the definition.
c. The updatable views and the advanced patterns
The updatable view. A view is updatable if the database can map the INSERT, the UPDATE, and the DELETE on the view to the underlying table. The conditions are: the view is based on a single table, the view does not have the aggregates, the DISTINCT, the GROUP BY, or the UNION, and the view includes the primary key.
CREATE VIEW active_employees AS
SELECT id, name, department, salary
FROM employees
WHERE active = true;The active_employees view is updatable because it is based on a single table and includes the primary key. The UPDATE active_employees SET salary = salary * 1.1 WHERE department = 'Engineering' updates the underlying employees table, and the WHERE active = true is applied automatically.
The WITH CHECK OPTION prevents the updates that would make the row fall outside the view:
CREATE VIEW active_employees AS
SELECT id, name, department, salary
FROM employees
WHERE active = true
WITH CHECK OPTION;The WITH CHECK OPTION means the INSERT or the UPDATE through the view must satisfy the WHERE active = true. The row that would be inactive is rejected.
The view over the view. A view can reference another view:
CREATE VIEW department_summary AS
SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary
FROM active_employees
GROUP BY department;The department_summary view references the active_employees view. The layering is the way to build the abstractions on the abstractions.
The materialized view. The materialized view stores the data, and the data must be refreshed. The syntax is the CREATE MATERIALIZED VIEW, and the refresh is the REFRESH MATERIALIZED VIEW.
CREATE MATERIALIZED VIEW customer_totals_mat AS
SELECT
c.id AS customer_id,
c.name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS lifetime_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
REFRESH MATERIALIZED VIEW customer_totals_mat;
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals_mat;The materialized view is the performance optimization for the expensive queries. The CONCURRENTLY refreshes the view without locking the readers, and it requires a unique index on the view.
The security with the views. The view is the security boundary. The user is granted the SELECT on the view, and the access to the underlying table is denied.
CREATE VIEW employee_public AS
SELECT id, name, department
FROM employees;
GRANT SELECT ON employee_public TO app_user;
REVOKE ALL ON employees FROM app_user;The app_user can select from the employee_public view but cannot access the employees table. The salary and the social security number are hidden, and the view is the only access.
The view with the parameters. A view cannot take the parameters, but a function can. The CREATE FUNCTION with the RETURNS TABLE is the parameterized view:
CREATE FUNCTION orders_since(p_date date)
RETURNS TABLE (id int, order_date date, total numeric) AS $$
SELECT id, order_date, total
FROM orders
WHERE order_date >= p_date
$$ LANGUAGE sql;The orders_since('2024-01-01') returns the orders since the date. The function is the parameterized view, and it is the standard pattern for the reusable queries with the parameters.
Complete Example Session
-- ============================================
-- PART 1: THE BASIC VIEW
-- ============================================
CREATE VIEW employee_public AS
SELECT id, name, department
FROM employees;
SELECT * FROM employee_public;
-- ============================================
-- PART 2: THE VIEW WITH THE EXPLICIT COLUMNS
-- ============================================
CREATE VIEW employee_public (id, full_name, dept) AS
SELECT id, name, department
FROM employees;
-- ============================================
-- PART 3: THE CREATE OR REPLACE
-- ============================================
CREATE OR REPLACE VIEW employee_public AS
SELECT id, name, department, hire_date
FROM employees;
-- ============================================
-- PART 4: THE VIEW WITH THE JOINS
-- ============================================
CREATE VIEW order_details AS
SELECT
o.id AS order_id,
o.order_date,
c.name AS customer_name,
p.name AS product_name,
oi.quantity,
oi.price,
oi.quantity * oi.price AS line_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;
SELECT * FROM order_details WHERE order_id = 1;
-- ============================================
-- PART 5: THE VIEW WITH THE AGGREGATES
-- ============================================
CREATE VIEW customer_totals AS
SELECT
c.id AS customer_id,
c.name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS lifetime_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
SELECT * FROM customer_totals ORDER BY lifetime_total DESC;
-- ============================================
-- PART 6: THE UPDATABLE VIEW
-- ============================================
CREATE VIEW active_employees AS
SELECT id, name, department, salary
FROM employees
WHERE active = true;
UPDATE active_employees SET salary = salary * 1.1 WHERE department = 'Engineering';
-- ============================================
-- PART 7: THE WITH CHECK OPTION
-- ============================================
CREATE VIEW active_employees AS
SELECT id, name, department, salary
FROM employees
WHERE active = true
WITH CHECK OPTION;
INSERT INTO active_employees (id, name, department, salary)
VALUES (100, 'Alice', 'Engineering', 100000);
-- The active column is not in the view, so it uses the default (true).
-- ============================================
-- PART 8: THE VIEW OVER THE VIEW
-- ============================================
CREATE VIEW department_summary AS
SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary
FROM active_employees
GROUP BY department;
SELECT * FROM department_summary;
-- ============================================
-- PART 9: THE MATERIALIZED VIEW
-- ============================================
CREATE MATERIALIZED VIEW customer_totals_mat AS
SELECT
c.id AS customer_id,
c.name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS lifetime_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
REFRESH MATERIALIZED VIEW customer_totals_mat;
SELECT * FROM customer_totals_mat ORDER BY lifetime_total DESC;
-- ============================================
-- PART 10: THE LFCS PIPELINE
-- ============================================
-- The security with the view
CREATE VIEW employee_public AS
SELECT id, name, department
FROM employees;
GRANT SELECT ON employee_public TO app_user;
REVOKE ALL ON employees FROM app_user;
-- The view management
ALTER VIEW employee_public RENAME TO employee_directory;
DROP VIEW IF EXISTS employee_directory;
-- The view listing
SELECT schemaname, viewname, viewowner
FROM pg_views
WHERE schemaname = 'public';
-- The view definition
SELECT pg_get_viewdef('employee_public', true);
-- The materialized view refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals_mat;The ten parts show the basic view, the explicit columns, the CREATE OR REPLACE, the view with the joins, the view with the aggregates, the updatable view, the WITH CHECK OPTION, the view over the view, the materialized view, and the LFCS pipeline.
Quick Reference
The View Statements
| Statement | Purpose |
|---|---|
CREATE VIEW name AS query | Create a view |
CREATE OR REPLACE VIEW name AS query | Create or replace |
CREATE VIEW IF NOT EXISTS | Create if missing |
CREATE TEMP VIEW | The session-only view |
ALTER VIEW name RENAME TO | Rename |
DROP VIEW name | Remove |
DROP VIEW IF EXISTS name | Remove if exists |
DROP VIEW name CASCADE | Remove with the dependents |
The View Definition
| Element | Meaning |
|---|---|
CREATE VIEW name (col1, col2) AS | The explicit columns |
SELECT ... FROM ... | The query |
WHERE ... | The row filter |
WITH CHECK OPTION | The update filter |
WITH (security_barrier) | The security barrier (PostgreSQL) |
The View Listing
| Query | Purpose |
|---|---|
SELECT * FROM pg_views | The PostgreSQL views |
SELECT * FROM information_schema.views | The standard |
SELECT pg_get_viewdef('name', true) | The definition |
\d+ name | The psql description |
\dv | The psql view list |
The Materialized View
| Statement | Purpose |
|---|---|
CREATE MATERIALIZED VIEW | Create the materialized view |
REFRESH MATERIALIZED VIEW | Refresh the data |
REFRESH MATERIALIZED VIEW CONCURRENTLY | Refresh without the lock |
DROP MATERIALIZED VIEW | Remove |
The Updatable Conditions
| Condition | Meaning |
|---|---|
| The single table | No joins |
| No aggregates | No GROUP BY, no SUM |
No DISTINCT | No distinct |
No UNION | No union |
| The primary key included | The row identity |
The WITH CHECK OPTION | The update constraint |
Best Practices
✅ Do This:
-- Use the CREATE OR REPLACE for the updates
CREATE OR REPLACE VIEW employee_public AS ... -- ✅
-- Use the explicit column names
CREATE VIEW employee_public (id, name, dept) AS ... -- ✅
-- Use the views for the security
CREATE VIEW employee_public AS SELECT id, name FROM employees;
GRANT SELECT ON employee_public TO app_user; -- ✅
-- Use the materialized view for the expensive queries
CREATE MATERIALIZED VIEW customer_totals_mat AS ... -- ✅
-- Use the WITH CHECK OPTION for the updatable views
CREATE VIEW active_employees AS ... WITH CHECK OPTION; -- ✅
-- Use the views for the abstraction
CREATE VIEW order_details AS SELECT ... FROM the joins; -- ✅❌ Don’t Do This:
-- Don't use the SELECT * in the view
CREATE VIEW v AS SELECT * FROM employees; // the columns change // ❌
-- Don't use the OR REPLACE with the different column names
CREATE OR REPLACE VIEW v AS SELECT a, b FROM t; // the columns must match // ❌
-- Don't grant the access to the base table
GRANT SELECT ON employees TO app_user; // the view is bypassed // ❌
-- Don't use the materialized view without the refresh
CREATE MATERIALIZED VIEW mv AS ...; // the data is stale // ❌
-- Don't expect the view to have the indexes
-- The view has no indexes; the base table has them // ❌
-- Don't use the view for the write-heavy paths
-- The updatable views have the constraints // ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| The view is not updatable | The joins or the aggregates | Use the base table |
The OR REPLACE fails | The columns differ | Keep the columns the same |
| The materialized view is stale | No refresh | Refresh the view |
| The permission denied | The base table is revoked | Grant the view |
| The view is slow | The complex query | Use the materialized view |
The SELECT * breaks | The schema change | Use the explicit columns |
The CASCADE drops too much | The dependent views | Check the dependencies |
Real-World Examples
1. The Basic View
CREATE VIEW employee_public AS SELECT id, name, department FROM employees;2. The Replace
CREATE OR REPLACE VIEW employee_public AS SELECT id, name, department, hire_date FROM employees;3. The Join View
CREATE VIEW order_details AS SELECT ... FROM orders JOIN ...;4. The Aggregate View
CREATE VIEW customer_totals AS SELECT ... FROM customers LEFT JOIN orders ... GROUP BY ...;5. The Updatable View
CREATE VIEW active_employees AS SELECT ... FROM employees WHERE active = true;6. The Check Option
CREATE VIEW active_employees AS ... WITH CHECK OPTION;7. The Materialized View
CREATE MATERIALIZED VIEW customer_totals_mat AS ...;
REFRESH MATERIALIZED VIEW customer_totals_mat;8. The Security
GRANT SELECT ON employee_public TO app_user;
REVOKE ALL ON employees FROM app_user;9. The View Listing
SELECT schemaname, viewname FROM pg_views WHERE schemaname = 'public';10. The View Definition
SELECT pg_get_viewdef('employee_public', true);Visual
The View Resolution
┌──────────────────────────────────────────────────────────────┐
│ THE VIEW RESOLUTION │
│ │
│ CREATE VIEW order_details AS │
│ SELECT o.id, c.name, p.name, oi.quantity │
│ FROM orders o │
│ JOIN customers c ON c.id = o.customer_id │
│ JOIN order_items oi ON oi.order_id = o.id │
│ JOIN products p ON p.id = oi.product_id; │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ SELECT * FROM order_details WHERE id = 1 │ │
│ │ │ │ │
│ │ │ The database expands the view. │ │
│ │ ▼ │ │
│ │ SELECT o.id, c.name, p.name, oi.quantity │ │
│ │ FROM orders o │ │
│ │ JOIN customers c ON c.id = o.customer_id │ │
│ │ JOIN order_items oi ON oi.order_id = o.id │ │
│ │ JOIN products p ON p.id = oi.product_id │ │
│ │ WHERE o.id = 1; │ │
│ │ │ │
│ │ The view is the stored query, not the stored data. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘The View as the Abstraction Layer
┌──────────────────────────────────────────────────────────────┐
│ THE VIEW AS THE ABSTRACTION LAYER │
│ │
│ THE APPLICATIONS │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ SELECT * FROM order_details WHERE order_date > ... │ │
│ │ SELECT * FROM customer_totals ORDER BY ... │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ │ The applications see the views. │
│ ▼ │
│ THE VIEWS │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ order_details (the joins, the computed columns) │ │
│ │ customer_totals (the aggregates) │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ │ The views see the tables. │
│ ▼ │
│ THE TABLES │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ orders, customers, order_items, products │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The views decouple the applications from the schema. │
│ The schema can change; the views present the stable shape. │
│ │
└──────────────────────────────────────────────────────────────┘The View as the Security Boundary
┌──────────────────────────────────────────────────────────────┐
│ THE VIEW AS THE SECURITY BOUNDARY │
│ │
│ THE EMPLOYEES TABLE │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ id, name, department, salary, ssn, password_hash │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ │ The view exposes only the safe columns. │
│ ▼ │
│ THE EMPLOYEE_PUBLIC VIEW │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ id, name, department │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ │ The grant is on the view, not the table. │
│ ▼ │
│ THE APP_USER │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ GRANT SELECT ON employee_public TO app_user; │ │
│ │ REVOKE ALL ON employees FROM app_user; │ │
│ │ │ │
│ │ The app_user can see the view but not the table. │ │
│ │ The salary and the ssn are hidden. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘The Materialized View
┌──────────────────────────────────────────────────────────────┐
│ THE MATERIALIZED VIEW │
│ │
│ THE REGULAR VIEW: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The query is stored. │ │
│ │ The query runs on every reference. │ │
│ │ The data is always current. │ │
│ │ The performance depends on the query. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ THE MATERIALIZED VIEW: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The query is stored. │ │
│ │ The query runs once, at the creation. │ │
│ │ The RESULT is stored. │ │
│ │ The data is STALE until the REFRESH. │ │
│ │ The performance is the table read. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ REFRESH MATERIALIZED VIEW customer_totals_mat; │ │
│ │ │ │
│ │ The refresh re-runs the query and updates the │ │
│ │ stored data. The CONCURRENTLY option allows the │ │
│ │ readers during the refresh. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| View | A named query that behaves like a table |
CREATE VIEW | Define the view |
CREATE OR REPLACE VIEW | Create or replace |
ALTER VIEW | Rename, change the owner |
DROP VIEW | Remove |
WITH CHECK OPTION | The update constraint |
| Updatable view | The single table, no aggregates |
| Materialized view | The stored data, refreshed |
pg_views | The view listing |
pg_get_viewdef | The view definition |
Key takeaways:
- A view is a named query that behaves like a table. It does not store the data — it stores the query — and the database executes the query when the view is referenced. The view is the abstraction that hides the complexity of the joins and the aggregates .
- The
CREATE OR REPLACE VIEWreplaces the definition without dropping the view. The grants on the view are preserved, which is the advantage over theDROPand theCREATE. The column list must match the existing view . - A view is updatable if the database can map the operations to the underlying table. The conditions are: the single table, no aggregates, no
DISTINCT, noUNION, and the primary key included. TheWITH CHECK OPTIONprevents the updates that would make the row fall outside the view . - The materialized view stores the data. The data is stale until the
REFRESH MATERIALIZED VIEW. TheCONCURRENTLYoption refreshes the view without locking the readers, and it requires a unique index on the view . - The views are the security boundary. The user is granted the
SELECTon the view, and the access to the underlying table is denied. The view exposes only the columns the user is allowed to see . - The views decouple the applications from the schema. The schema can change, and the view presents the stable shape. The
CREATE OR REPLACE VIEWupdates the view without breaking the applications . - The views can be layered. A view can reference another view. The layering is the way to build the abstractions on the abstractions, and the
CASCADEdrop removes the dependents . - The views do not have the indexes. The indexes are on the base tables. The complex view re-executes the query on every reference, and the performance depends on the underlying query and the indexes on the base tables .
Remember: A view is a named query that behaves like a table. It stores the query, not the data, and the database executes the query when the view is referenced. The CREATE OR REPLACE VIEW replaces the definition without the drop, and the WITH CHECK OPTION constrains the updatable views. The materialized view stores the data, and the REFRESH updates it. The views are the security boundary, the abstraction layer, and the compatibility bridge. The result is the encapsulation of the complex queries behind the simple names, which is the foundation of the database design.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!