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

Refreshing a Materialized View in PostgreSQL

📖 Scenario: You manage a sales database that stores daily sales data in a table. To speed up reporting, you create a materialized view that summarizes total sales by product. Since the sales data updates daily, you need to refresh the materialized view to keep the summary current.

🎯 Goal: Build a PostgreSQL setup where you create a materialized view summarizing sales totals by product, then write the SQL command to refresh this materialized view to update its data.

📋 What You'll Learn

Create a table called sales with columns product_id (integer) and amount (numeric).

Insert sample sales data into the sales table.

Create a materialized view called sales_summary that shows total sales amount per product_id.

Write the SQL command to refresh the sales_summary materialized view.

💡 Why This Matters

🌍 Real World

Materialized views are used in real-world databases to speed up complex queries by storing precomputed results. Refreshing them keeps the data accurate as the underlying tables change.

💼 Career

Database administrators and backend developers often use materialized views and refresh commands to optimize performance and maintain data consistency in production systems.

1

Create the sales table and insert data

Create a table called sales with columns product_id as integer and amount as numeric. Then insert these exact rows into sales: (1, 100.50), (2, 200.00), (1, 150.75).

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

Create the sales_summary materialized view

Create a materialized view called sales_summary that shows the total amount of sales grouped by product_id from the sales table.

PostgreSQL

CREATE TABLE sales (
    product_id INTEGER,
    amount NUMERIC
);

INSERT INTO sales (product_id, amount) VALUES
(1, 100.50),
(2, 200.00),
(1, 150.75);

-- Create the materialized view sales_summary
-- Your code here

Need a hint?

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

3

Add a command to refresh the materialized view

Write the SQL command to refresh the materialized view called sales_summary.

PostgreSQL

CREATE TABLE sales (
    product_id INTEGER,
    amount NUMERIC
);

INSERT INTO sales (product_id, amount) VALUES
(1, 100.50),
(2, 200.00),
(1, 150.75);

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

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

Need a hint?

Use REFRESH MATERIALIZED VIEW view_name; to update the data.

4

Complete the setup with a comment explaining refresh use

Add a SQL comment explaining that REFRESH MATERIALIZED VIEW sales_summary; updates the summary data to reflect changes in the sales table.

PostgreSQL

CREATE TABLE sales (
    product_id INTEGER,
    amount NUMERIC
);

INSERT INTO sales (product_id, amount) VALUES
(1, 100.50),
(2, 200.00),
(1, 150.75);

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

REFRESH MATERIALIZED VIEW sales_summary;

-- Add a comment explaining the refresh
-- Your code here

Need a hint?

Use a SQL comment starting with -- to explain the refresh command.