CREATE MATERIALIZED VIEW in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple materialized view query

Given the table sales with columns id, amount, and region, what is the output of the following query after creating the materialized view?

CREATE MATERIALIZED VIEW regional_sales AS SELECT region, SUM(amount) AS total FROM sales GROUP BY region;

Then running:
SELECT * FROM regional_sales ORDER BY region;

PostgreSQL

CREATE MATERIALIZED VIEW regional_sales AS SELECT region, SUM(amount) AS total FROM sales GROUP BY region;

SELECT * FROM regional_sales ORDER BY region;

A []

B [{"region": "East", "total": 3000}, {"region": "West", "total": 4500}]

C SyntaxError: materialized view not refreshed

D [{"region": "East", "total": 0}, {"region": "West", "total": 0}]

💡 Hint

Materialized views store the result of the query at creation time.

The materialized view stores the sum of amounts grouped by region at creation. Querying it returns the precomputed totals.

📝 Syntax

intermediate

Identify the syntax error in materialized view creation

Which option contains a syntax error when creating a materialized view in PostgreSQL?

A CREATE MATERIALIZED VIEW sales_summary SELECT region, COUNT(*) AS count FROM sales GROUP BY region;

B CREATE MATERIALIZED VIEW sales_summary AS SELECT region, COUNT(*) AS count FROM sales GROUP BY region;

C CREATE MATERIALIZED VIEW sales_summary AS SELECT region, COUNT(*) FROM sales GROUP BY region;

D CREATE MATERIALIZED VIEW sales_summary AS SELECT region, COUNT(*) AS count FROM sales GROUP BY region WITH DATA;

💡 Hint

Check for missing keywords in the statement.

Option A is missing the keyword 'AS' before the SELECT statement, which is required syntax.

❓ optimization

advanced

Best use of materialized view refresh

You have a materialized view that summarizes daily sales. Which option correctly refreshes the materialized view without locking reads during the refresh?

A REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

B ALTER MATERIALIZED VIEW sales_summary REFRESH;

C UPDATE MATERIALIZED VIEW sales_summary;

D REFRESH MATERIALIZED VIEW sales_summary;

💡 Hint

Look for the option that allows concurrent reads during refresh.

REFRESH MATERIALIZED VIEW CONCURRENTLY allows the view to be refreshed without blocking reads.

🔧 Debug

advanced

Why does this materialized view query fail?

You created a materialized view with:
CREATE MATERIALIZED VIEW mv AS SELECT id, amount FROM sales;
Later, you run:
SELECT * FROM mv WHERE amount > 100;
But you get outdated results after updating the sales table. Why?

A You must recreate the materialized view after every update to the base table.

B The WHERE clause is invalid on materialized views.

C Materialized views do not update automatically; you must refresh them manually.

D Materialized views cannot select columns with numeric data types.

💡 Hint

Think about how materialized views store data.

Materialized views store a snapshot of data at creation or last refresh. They do not update automatically when base tables change.

🧠 Conceptual

expert

Materialized view storage and performance trade-off

Which statement best describes the main trade-off when using materialized views in PostgreSQL?

A Materialized views are faster than indexes and replace them entirely.

B Materialized views automatically update with base tables but slow down inserts.

C Materialized views do not store data and always compute results on the fly.

D Materialized views improve query speed by storing results but require manual refresh, which can be costly.

💡 Hint

Consider how materialized views store data and update it.

Materialized views store query results physically, speeding up reads but need manual refresh which can be resource intensive.