Skip to content
SQLViews, Procedures, and Securityadvanced22 min read

Creating and Using Views

Learn to encapsulate complex SQL queries in reusable views, restrict access to sensitive columns, and use materialized views for reporting workloads in PostgreSQL.

On This Page (Table of Contents)

In this lesson, you will learn

  • ✓Explain what a database view is and how it differs from a table
  • ✓Create, query, replace, and remove views using PostgreSQL
  • ✓Simplify application queries by encapsulating joins and calculations
  • ✓Use views to expose selected columns while protecting sensitive information
  • ✓Understand view ownership, privileges, and security-related options
  • ✓Create and refresh materialized views for reporting and analytics
  • ✓Compare ordinary views and materialized views, including their performance trade-offs

Introduction to Database Views

A view is a named SQL query that can be queried like a table.

Imagine an application repeatedly needs customer names, their order IDs, and order totals. Without a view, the application might have to repeat the same JOIN in several places.

A view allows you to define that query once and reuse it.

A regular view generally stores the query definition, not a separate copy of all its result rows. When you query the view, PostgreSQL plans and executes the underlying query as needed.

Views are useful for:
- Simplifying complicated joins.
- Reusing commonly needed query logic.
- Presenting data in a consistent structure.
- Exposing selected columns to application roles.
- Creating stable, understandable interfaces over underlying tables.

A view does not automatically make a query faster or guarantee that sensitive information is protected. Performance depends on the underlying query and execution plan, while security depends on the database's permissions and view configuration.

Create Your First View

Use CREATE VIEW to define a regular view.

The following example creates a temporary customers table and defines a view that exposes customer names and cities.

Run the statements in order in the same PostgreSQL session. The temporary table exists only for that session, and the view depends on it.

Create and Query a View

Create and Query a View
sql
CREATE TEMP TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    city TEXT NOT NULL
);
 
INSERT INTO customers (customer_id, name, email, city)
VALUES
    (1, 'Alex', 'alex@example.com', 'Lucknow'),
    (2, 'Riya', 'riya@example.com', 'Noida'),
    (3, 'Sam', 'sam@example.com', 'Delhi');
 
CREATE TEMP VIEW customer_directory AS
SELECT customer_id, name, city
FROM customers;
 
SELECT customer_id, name, city
FROM customer_directory
ORDER BY customer_id;
Output
customer_id | name | city -------------+------+--------- 1 | Alex | Lucknow 2 | Riya | Noida 3 | Sam | Delhi (3 rows)

The customer_directory view exposes three columns from the customers table. The query against the view returns the underlying customer's current values. The view does not create an independent copy of those rows. The example deliberately excludes email from the view. This can help limit what an application role is allowed to access, provided that role's underlying table permissions and view permissions are configured correctly.

Views for Complex Joins

Views can encapsulate joins so that applications do not need to repeat the same SQL in multiple places.

For example, an order-reporting view can combine orders with customer names.

The view acts as a reusable query interface. If the application consistently uses the view, its SQL can be simpler and easier to maintain.

However, a regular view is not automatically a performance cache. PostgreSQL still needs to execute the underlying query when the view is queried.

Views also do not automatically eliminate the need to understand joins. An INNER JOIN returns matching rows, while a LEFT JOIN retains rows from the left-hand table even when no matching right-hand row exists.

Practical Example: Customer Order Report

Create an orders table and define a view that combines customer information with order details.

The example uses a LEFT JOIN so customers can still appear in the view when they have no orders.

Create a Reporting View

Create a Reporting View
sql
CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    total_amount NUMERIC(10, 2) NOT NULL
);
 
INSERT INTO orders (order_id, customer_id, total_amount)
VALUES
    (101, 1, 1200.00),
    (102, 1, 800.00),
    (103, 2, 1500.00);
 
CREATE TEMP VIEW customer_order_report AS
SELECT
    c.customer_id,
    c.name AS customer_name,
    o.order_id,
    o.total_amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id;
 
SELECT customer_name, order_id, total_amount
FROM customer_order_report
ORDER BY customer_name, order_id;
Output
customer_name | order_id | total_amount ---------------+----------+------------- Alex | 101 | 1200.00 Alex | 102 | 800.00 Riya | 103 | 1500.00 Sam | NULL | NULL (4 rows)

The view combines customer and order data. Alex appears twice because Alex has two orders. Sam appears once with NULL order values because the LEFT JOIN retains customers without matching orders. The view stores the query definition, not a separate permanent copy of the joined result.

Replace and Remove Views

Use CREATE OR REPLACE VIEW to update a view's query definition.

In PostgreSQL, replacing an existing view generally requires the existing output columns to remain compatible: existing columns must retain their names, order, and data types. New columns can be appended in supported cases.

Use DROP VIEW to remove a regular view when it is no longer needed.

Avoid using CASCADE casually because dependent database objects may also be removed.

Update a View Definition

Update a View Definition
sql
CREATE OR REPLACE TEMP VIEW customer_directory AS
SELECT customer_id, name, city
FROM customers
WHERE city <> 'Delhi';
 
SELECT customer_id, name, city
FROM customer_directory
ORDER BY customer_id;
 
-- Run only when the view is no longer needed:
-- DROP VIEW customer_directory;
Output
customer_id | name | city -------------+------+--------- 1 | Alex | Lucknow 2 | Riya | Noida (2 rows)

The replacement definition adds a filter to the view. Queries against customer_directory now return only customers whose city is not Delhi. The underlying customers table is not changed by this view definition. The DROP statement is commented out so that the view remains available for the next examples.

Views and Data Security

Views can help limit which columns or rows an application role can access.

Suppose the customers table contains:
- customer_id
- name
- email
- city

A reporting role may need customer names and cities but should not access email addresses. You can expose only the required columns through a view and grant that role access to the view instead of the base table.

However, merely creating a view does not secure the data. You must also configure privileges correctly.

Important practices:
1. Use separate database roles for different responsibilities.
2. Grant only the required privileges.
3. Avoid granting direct access to sensitive base tables when it is unnecessary.
4. Understand the view owner's privileges and PostgreSQL's view-security behavior.
5. Consider row-level security when access must be restricted by individual rows.
6. Test access using the actual application role, not only an administrator account.

PostgreSQL supports security-related view options such as security_barrier and, in supported versions, security_invoker. These options address different security concerns and are not interchangeable.

Create a View with Limited Columns

This example exposes customer identifiers, names, and cities while excluding email.

The view's selected columns define its output, but the role's privileges determine whether it can query the view or access the underlying table directly.

Expose Only Required Customer Data

Expose Only Required Customer Data
sql
CREATE TEMP VIEW public_customer_directory AS
SELECT customer_id, name, city
FROM customers;
 
SELECT *
FROM public_customer_directory
ORDER BY customer_id;
Output
customer_id | name | city -------------+------+--------- 1 | Alex | Lucknow 2 | Riya | Noida 3 | Sam | Delhi (3 rows)

The view omits email, so email is not part of this view's result. This is a demonstration of column selection, not a complete production security configuration. Temporary views and temporary tables are session-scoped, and production access control should be tested with appropriate permanent schemas, roles, grants, and view options.

Grant Access to a View

In a permanent PostgreSQL schema, an administrator can grant a reporting role access to a view.

For example, after creating a permanent view in an application schema, a database administrator might run:

GRANT USAGE ON SCHEMA reporting TO reporting_reader;
GRANT SELECT ON reporting.customer_directory TO reporting_reader;

If the role should not read the base table directly, do not grant it SELECT on that base table.

PostgreSQL's default view behavior commonly checks underlying table privileges using the view owner's rights. The view owner's privileges and security configuration must therefore be reviewed carefully.

If security_invoker is enabled on a supported PostgreSQL version, underlying permissions are checked using the invoking user's rights. That can change whether a role can query the view.

Schema names, roles, and grants must be adapted to the real database. A GRANT on a view is not a substitute for checking access to the base tables and other database objects.

Regular Views Versus Materialized Views

A regular view and a materialized view both provide named query results, but they store data differently.

Regular view:
- Stores the query definition.
- Computes results when queried.
- Does not maintain a separate stored result set.
- Reflects underlying table changes according to normal transaction visibility.

Materialized view:
- Stores the result of its defining query.
- Can make repeated, expensive reporting queries faster.
- Uses additional storage.
- Can become stale when underlying tables change.
- Requires REFRESH MATERIALIZED VIEW to update its stored results.

Choose a regular view when you want reusable query logic and results based on current underlying data.

Consider a materialized view when the query is expensive, repeated frequently, and the application can tolerate data that is refreshed on a schedule or by an explicit process.

Create a Materialized View

Use CREATE MATERIALIZED VIEW to store the result of a query.

The following example creates a summary of order totals per customer. It uses the permanent or temporary customers and orders tables created earlier in this lesson.

The materialized view contains the results at the time it is populated; it does not automatically update after changes to orders.

Build a Stored Order Summary

Build a Stored Order Summary
sql
CREATE TEMP MATERIALIZED VIEW customer_sales_summary AS
SELECT
    c.customer_id,
    c.name AS customer_name,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_sales
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;
 
SELECT customer_name, order_count, total_sales
FROM customer_sales_summary
ORDER BY customer_name;
Output
customer_name | order_count | total_sales ---------------+-------------+------------ Alex | 2 | 2000.00 Riya | 1 | 1500.00 Sam | 0 | 0.00 (3 rows)

The materialized view stores one summary row per customer. COUNT(o.order_id) returns zero for Sam because COUNT of a NULL-extended order_id does not count that row. COALESCE converts the NULL sum for a customer with no orders into zero. The stored result remains unchanged when the underlying orders table changes until the materialized view is refreshed.

Refresh a Materialized View

Use REFRESH MATERIALIZED VIEW to recompute and replace the stored results.

The example inserts another order for Sam, checks the materialized view before refreshing it, refreshes the view, and checks the updated results.

Run these statements in the same session as the preceding temporary-table and materialized-view examples.

Update the Stored Summary

Update the Stored Summary
sql
INSERT INTO orders (order_id, customer_id, total_amount)
VALUES (104, 3, 600.00);
 
-- The stored summary is not automatically updated.
SELECT customer_name, order_count, total_sales
FROM customer_sales_summary
WHERE customer_name = 'Sam';
 
REFRESH MATERIALIZED VIEW customer_sales_summary;
 
SELECT customer_name, order_count, total_sales
FROM customer_sales_summary
WHERE customer_name = 'Sam';
Output
Before refresh: customer_name | order_count | total_sales ---------------+-------------+------------ Sam | 0 | 0.00 (1 row) After refresh: customer_name | order_count | total_sales ---------------+-------------+------------ Sam | 1 | 600.00 (1 row)

The INSERT changes the orders table, but it does not automatically recompute the materialized view. REFRESH MATERIALIZED VIEW reruns the defining query and updates the stored results. The second SELECT therefore shows Sam's new order. A refresh can be expensive for large reporting queries. Plan refresh frequency according to data-freshness requirements and system load.

Concurrent Refresh and Index Requirements

For suitable permanent materialized views, PostgreSQL supports REFRESH MATERIALIZED VIEW CONCURRENTLY.

This option is designed to let concurrent SELECT statements continue reading the materialized view during refresh. It has restrictions:
- The materialized view must already be populated.
- It must have a qualifying UNIQUE index that uses only column names and includes all rows; the index cannot be partial or an expression index.
- Only one refresh at a time can run against a given materialized view.

For example, a permanent summary with one row per customer might use:

CREATE UNIQUE INDEX customer_sales_summary_customer_id_uq
ON reporting.customer_sales_summary (customer_id);

Then, if the index satisfies PostgreSQL's requirements, the materialized view can be refreshed with:

REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.customer_sales_summary;

These statements are illustrative production-schema examples. They cannot be applied directly to the temporary materialized view used earlier because the concurrent-refresh restrictions and schema requirements differ.

Concurrent refresh reduces some reader blocking but can require additional resources and work. It does not make refresh free or instantaneous.

Performance and Indexing Views

A regular view does not automatically have its own indexes. Its query generally uses the indexes available on the underlying tables.

For example, if a view joins orders to customers on customer_id, a suitable index on the underlying orders.customer_id column may help the join.

A materialized view stores its result and can have indexes of its own. These indexes can support searches against the stored summary.

Use EXPLAIN to inspect the planned query and EXPLAIN ANALYZE to measure actual execution in a safe environment. Remember that EXPLAIN ANALYZE executes the statement.

A useful decision process is:
1. Measure the original query.
2. Consider whether a regular view would improve maintainability.
3. If repeated computation is expensive, assess a materialized view.
4. Define an acceptable freshness window.
5. Add indexes to the underlying tables or materialized view where justified.
6. Measure read, refresh, and storage costs.
7. Revisit the design as the workload changes.

Common Mistakes

Common mistakes when working with views include:

- Assuming a regular view stores a separate copy of its data.
- Expecting a materialized view to refresh automatically.
- Assuming a view alone provides complete security.
- Forgetting that a view depends on underlying tables and objects.
- Assuming every view makes a query faster.
- Using CASCADE without checking dependent objects.
- Running refresh operations without considering reporting latency and resource usage.

Choose the right view type, configure privileges deliberately, and test behavior against the actual database roles and workload.

Practice Exercises

Exercise 1: Create a view
Create a view that exposes customer_id, name, and city from the customers table.

Exercise 2: Join-based view
Create a view that combines customer names with their orders using a JOIN.

Exercise 3: Replace a view
Update a view so that it excludes customers from a selected city. Explain why the view's output columns must remain compatible when using CREATE OR REPLACE VIEW.

Exercise 4: Security
A reporting role needs customer names but must not read email addresses. Explain how a view and appropriate privileges can support this requirement.

Exercise 5: Materialized view
Create a materialized view that stores total sales per customer. Insert a new order and explain why the stored summary does not immediately change.

Exercise 6: Refresh
Refresh the materialized view and verify that its stored results reflect the new order.

Exercise 7: Performance
Explain why a regular view over an expensive join does not automatically make that join faster.

Exercise 8: Concurrent refresh
Describe the main purpose of REFRESH MATERIALIZED VIEW CONCURRENTLY and identify its unique-index requirement.

Knowledge Check

Question 1: What does a regular PostgreSQL view primarily store?

A. A permanent copy of every result row.
B. A query definition.
C. An independent database server.
D. A backup of the underlying tables.

Answer: B. A regular view stores a query definition rather than a separate materialized result set.

Question 2: What is the main difference between a regular view and a materialized view?

A. A regular view stores query results, while a materialized view stores only SQL.
B. A materialized view stores query results that must be refreshed.
C. Neither can be queried with SELECT.
D. A materialized view cannot use indexes.

Answer: B. Materialized views store results and need refreshes to reflect later source changes.

Question 3: Does creating a view automatically protect sensitive columns?

A. Yes, regardless of permissions.
B. No. Access privileges and view security configuration must also be correct.
C. Yes, but only when the table has a primary key.
D. No view can be used for access control.

Answer: B. A view can expose selected columns, but effective protection requires correct database privileges and security configuration.

Question 4: Which command recomputes a materialized view's stored results?

A. UPDATE VIEW
B. COMMIT VIEW
C. REFRESH MATERIALIZED VIEW
D. REBUILD TABLE

Answer: C. REFRESH MATERIALIZED VIEW recomputes the stored result set.

Question 5: What is one benefit of a materialized view?

A. It automatically updates after every underlying table change.
B. It can avoid repeating an expensive query for every report request.
C. It eliminates all storage costs.
D. It guarantees every query is faster.

Answer: B. Stored results can accelerate repeated reporting queries, but they have freshness and maintenance costs.

Question 6: What is a key requirement for REFRESH MATERIALIZED VIEW CONCURRENTLY?

A. The view must have no rows.
B. The view must already be populated and have a qualifying unique index.
C. The underlying tables must contain no indexes.
D. It must run inside a transaction block.

Answer: B. PostgreSQL requires a populated materialized view and a qualifying unique index for concurrent refresh.

Question 7: Can a regular view use indexes on its underlying tables?

A. No.
B. Yes, PostgreSQL can use suitable indexes when planning the underlying query.
C. Only if the view is materialized.
D. Only if the view has a primary key.

Answer: B. A regular view's underlying query can benefit from indexes on the source tables.

Interview Questions and Answers

Q1. What is a database view?

Answer:
A view is a named query that can be queried like a table. A regular view generally stores the query definition rather than a separate copy of its result rows.

Q2. Why do developers use views?

Answer:
Views simplify repeated queries, encapsulate joins and calculations, create reusable data interfaces, and can expose selected columns or rows to application roles.

Q3. What is the difference between a table and a regular view?

Answer:
A table stores data rows. A regular view stores a query definition and derives its results from the underlying objects when queried.

Q4. What is the difference between a regular view and a materialized view?

Answer:
A regular view computes results through its underlying query when accessed. A materialized view stores the result set and must be refreshed to reflect later source changes.

Q5. How do you create a view in PostgreSQL?

Answer:
Use CREATE VIEW view_name AS SELECT ...;. Use CREATE OR REPLACE VIEW when updating a compatible existing view definition.

Q6. Can a view improve performance?

Answer:
A regular view can improve maintainability but does not automatically cache results or speed up the query. A materialized view can accelerate repeated expensive reporting queries by storing results, at the cost of storage and refresh work.

Q7. How can views help with database security?

Answer:
A view can expose only required columns or rows, and permissions can grant access to the view without granting direct access to the base table. The owner, grants, view options, and underlying privileges must be configured correctly.

Q8. What is security_invoker in PostgreSQL?

Answer:
It is a view option, available in supported PostgreSQL versions, that causes underlying permission checks to use the invoking user rather than the view owner. It affects view access semantics and should be evaluated alongside the database's other security rules.

Q9. What does REFRESH MATERIALIZED VIEW do?

Answer:
It reruns the materialized view's defining query and replaces its stored results with the newly computed results.

Q10. What is REFRESH MATERIALIZED VIEW CONCURRENTLY?

Answer:
It refreshes a materialized view in a way that permits concurrent SELECT statements to continue reading it. It requires the view to be populated and to have a qualifying unique index.

Q11. Can you create an index directly on a regular view?

Answer:
A regular PostgreSQL view does not have its own stored result rows to index. You generally index the underlying tables. A materialized view stores rows and can have indexes of its own.

Q12. What are the limitations of views?

Answer:
Views depend on their underlying objects, can conceal query complexity, do not automatically guarantee performance or security, and may have restrictions on updates or changes to their definitions. Materialized views additionally require storage and refresh management.

Lesson Summary

In this lesson, you learned how PostgreSQL views support reusable SQL and reporting.

Remember:
- CREATE VIEW defines a reusable query.
- Regular views generally do not store separate copies of result rows.
- Views can encapsulate joins and present selected columns.
- Effective access control requires correct roles, privileges, and view security configuration.
- Materialized views store query results and require refreshes.
- REFRESH MATERIALIZED VIEW CONCURRENTLY has specific unique-index and population requirements.
- Regular views can use indexes on underlying tables, while materialized views can have their own indexes.
- Measure performance and plan refresh frequency according to workload and data-freshness requirements.

Use regular views to organize query logic and materialized views when stored reporting results provide a measurable benefit.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Assuming a regular view stores a separate copy of the data

Why it happens: A view looks and behaves like a table when queried, so developers may assume it stores its own rows.

Incorrect Approach
sql
sql
CREATE VIEW customer_directory AS
SELECT customer_id, name, city
FROM customers;
 
-- Incorrect assumption:
-- The view contains an independently stored copy
-- of the customers table.
Correct Solution
sql
sql
-- A regular view stores a query definition.
SELECT customer_id, name, city
FROM customer_directory;
 
-- Use a materialized view when a stored result set
-- is needed and refresh management is acceptable.

Key takeaway: A regular view computes its result from the underlying query when accessed. A materialized view stores its result rows separately.

#2 Assuming a materialized view updates automatically

Why it happens: Developers expect a stored summary to behave like a regular view over changing source tables.

Incorrect Approach
sql
sql
CREATE MATERIALIZED VIEW sales_summary AS
SELECT customer_id, SUM(total_amount) AS total_sales
FROM orders
GROUP BY customer_id;
 
-- Later inserts into orders do not automatically
-- recompute this stored result.
Correct Solution
sql
sql
REFRESH MATERIALIZED VIEW sales_summary;
 
SELECT customer_id, total_sales
FROM sales_summary;

Key takeaway: Refresh the materialized view according to the required freshness window. Refreshing can consume significant resources on large datasets.

#3 Treating a view as a complete security boundary without configuring privileges

Why it happens: Developers believe excluding a sensitive column from a view is sufficient even when application roles can query the underlying table directly.

Incorrect Approach
sql
sql
CREATE VIEW customer_directory AS
SELECT customer_id, name, city
FROM customers;
 
-- The view alone does not revoke existing
-- permissions on the customers table.
Correct Solution
sql
sql
-- Illustrative statements for permanent objects
-- in an appropriately configured schema:
REVOKE SELECT ON TABLE app.customers
FROM reporting_reader;
 
GRANT USAGE ON SCHEMA reporting
TO reporting_reader;
 
GRANT SELECT ON reporting.customer_directory
TO reporting_reader;

Key takeaway: Adapt grants to the real schema, role membership, view ownership, and security options. Test the result using the intended application role. These example grants assume the view's configuration permits that role to query it.

#4 Using CASCADE without checking dependencies

Why it happens: Developers try to remove a view and overlook other database objects that depend on it.

Incorrect Approach
sql
sql
-- CASCADE can remove dependent objects.
DROP VIEW customer_directory CASCADE;
Correct Solution
sql
sql
-- First inspect dependencies and confirm
-- the view is no longer required.
DROP VIEW customer_directory;

Key takeaway: Prefer the restrictive default behavior unless dependent objects have been reviewed and their removal is intentional.

#5 Assuming a materialized view is always faster overall

Why it happens: The read performance benefit is considered without measuring refresh time, storage, and data-freshness requirements.

Incorrect Approach
sql
sql
-- Creating a materialized view does not
-- automatically make the complete workload faster.
CREATE MATERIALIZED VIEW sales_summary AS
SELECT customer_id, SUM(total_amount) AS total_sales
FROM orders
GROUP BY customer_id;
Correct Solution
sql
sql
-- Measure the reporting query and refresh cost.
EXPLAIN
SELECT customer_id, SUM(total_amount) AS total_sales
FROM orders
GROUP BY customer_id;
 
-- Evaluate stored results only when repeated reads,
-- refresh costs, and freshness requirements justify them.

Key takeaway: Evaluate both read performance and maintenance overhead using representative data and a realistic workload.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

  • A regular view stores a query definition rather than a separate copy of its result rows.
  • Views can make complex queries easier to reuse and maintain.
  • A view that excludes sensitive columns can reduce exposure, but correct permissions are still essential.
  • PostgreSQL views can have security implications involving ownership, privileges, and security options.
  • A materialized view stores query results and must be refreshed to reflect changes in its underlying data.
  • Materialized views can accelerate expensive reporting queries at the cost of storage and freshness.
  • Use EXPLAIN and realistic testing to evaluate performance rather than assuming a view automatically makes a query faster.
Was this lesson helpful?
Found an error? Report an issue