CREATE MATERIALIZED VIEW in PostgreSQL - Mini Project: Build & Apply

Create a Materialized View in PostgreSQL

📖 Scenario: You work in a company database team. Your manager wants a fast way to see the total sales per product category. The sales data is large and slow to query every time. To speed this up, you will create a materialized view that stores the summarized data.

🎯 Goal: Build a materialized view in PostgreSQL that shows total sales amount for each product category. This view will store the data physically for faster access.

📋 What You'll Learn

Create a table called sales with columns id, product_category, and amount.

Insert sample sales data into the sales table.

Create a materialized view called category_sales_summary that sums amount grouped by product_category.

Refresh the materialized view to update its data.

💡 Why This Matters

🌍 Real World

Materialized views help speed up reports and dashboards by storing pre-calculated results, reducing load on the database.

💼 Career

Database developers and administrators use materialized views to optimize query performance in large data environments.

1

Create the sales table and insert data

Create a table called sales with columns id as integer primary key, product_category as text, and amount as numeric. Then insert these exact rows: (1, 'Books', 15.50), (2, 'Electronics', 230.00), (3, 'Books', 22.75), (4, 'Clothing', 45.00).

PostgreSQL

-- Create the sales table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define the materialized view query

Create a SQL query that selects product_category and the sum of amount as total_sales from the sales table, grouped by product_category. Save this query as a string in a variable called summary_query.

PostgreSQL

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product_category TEXT,
    amount NUMERIC
);

INSERT INTO sales (id, product_category, amount) VALUES
(1, 'Books', 15.50),
(2, 'Electronics', 230.00),
(3, 'Books', 22.75),
(4, 'Clothing', 45.00);

-- Create a variable summary_query with the SQL string for grouping and summing sales
-- Your code here

Need a hint?

Use a SELECT statement with GROUP BY and SUM(). Store it as a string variable.

3

Create the materialized view using the query

Create a materialized view called category_sales_summary using the SQL query stored in summary_query. Use the exact query string from summary_query in the CREATE MATERIALIZED VIEW statement.

PostgreSQL

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product_category TEXT,
    amount NUMERIC
);

INSERT INTO sales (id, product_category, amount) VALUES
(1, 'Books', 15.50),
(2, 'Electronics', 230.00),
(3, 'Books', 22.75),
(4, 'Clothing', 45.00);

-- Create the materialized view category_sales_summary using the query
-- Your code here

Need a hint?

Use CREATE MATERIALIZED VIEW view_name AS SELECT ... with the grouping query.

4

Refresh the materialized view

Write a SQL command to refresh the materialized view called category_sales_summary so it updates its stored data.

PostgreSQL

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product_category TEXT,
    amount NUMERIC
);

INSERT INTO sales (id, product_category, amount) VALUES
(1, 'Books', 15.50),
(2, 'Electronics', 230.00),
(3, 'Books', 22.75),
(4, 'Clothing', 45.00);

CREATE MATERIALIZED VIEW category_sales_summary AS
SELECT product_category, SUM(amount) AS total_sales FROM sales GROUP BY product_category;

-- Refresh the materialized view category_sales_summary
-- Your code here

Need a hint?

Use REFRESH MATERIALIZED VIEW view_name; to update the data.