Store a Subset of Data with a Filtered Materialized View

Create a Materialized View with Filtering

Write a PostgreSQL query to create a materialized view that includes only active customers from the Customers table.

Solution:

-- Create a materialized view for active customers.
CREATE MATERIALIZED VIEW ActiveCustomers AS
SELECT customer_id, customer_name, status
FROM Customers
WHERE status = 'active';

Explanation:

• Purpose of the Query:
• The goal is to store a subset of data filtered by a condition, reducing the view’s size and focusing on relevant records.
• This demonstrates how to apply a WHERE clause in a materialized view definition.
• Key Components:
• WHERE status = 'active' : Filters the rows to include only active customers.
• Real-World Application:
• Helps in creating efficient dashboards that focus on current, actionable customer data.

Notes:

• The view will need to be refreshed to include new active customers as the underlying data changes.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a materialized view that selects only orders with a status of 'completed' from the Orders table.
• Write a PostgreSQL query to create a materialized view that includes only customers from a specified country in the Customers table.
• Write a PostgreSQL query to create a materialized view that filters products from the Products table to include only those with a stock quantity above a certain threshold.
• Write a PostgreSQL query to create a materialized view that displays only transactions exceeding $1000 from the Transactions table.

Go to:

• Optimizing Data with PostgreSQL Materialized Views Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : Create a Materialized View with a JOIN.
NEXT : Create a Materialized View with Sorting.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.