How to Create Materialized View in PostgreSQL: Syntax and Example

In PostgreSQL, you create a materialized view using the CREATE MATERIALIZED VIEW statement followed by the view name and a query. This stores the query result physically, improving performance for complex queries but requires manual refresh with REFRESH MATERIALIZED VIEW to update data.

Syntax

The basic syntax to create a materialized view in PostgreSQL is:

• CREATE MATERIALIZED VIEW view_name AS query [WITH [NO] DATA]; creates the materialized view with the result of the query.
• You can add WITH [NO] DATA to specify if the view should be populated immediately or not.

sql

CREATE MATERIALIZED VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition
WITH [NO] DATA;

Example

This example creates a materialized view named sales_summary that stores total sales per product from a sales table. It demonstrates how the view stores data physically and can be refreshed later.

sql

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

-- To see the data
SELECT * FROM sales_summary;

-- To refresh the materialized view when data changes
REFRESH MATERIALIZED VIEW sales_summary;

Output

 product_id | total_sales 
------------+-------------
          1 |       15000
          2 |       23000
          3 |       12000
(3 rows)

Common Pitfalls

Common mistakes when using materialized views include:

• Expecting the view to update automatically when underlying tables change. You must run REFRESH MATERIALIZED VIEW manually.
• Creating the view with WITH NO DATA and forgetting to populate it before querying.
• Not considering the storage cost since data is physically saved.

sql

/* Wrong: Querying before populating */
CREATE MATERIALIZED VIEW mv_example AS
SELECT * FROM some_table
WITH NO DATA;

SELECT * FROM mv_example; -- returns no rows

/* Right: Refresh before querying */
REFRESH MATERIALIZED VIEW mv_example;
SELECT * FROM mv_example;

Quick Reference

Command	Description
CREATE MATERIALIZED VIEW view_name AS query	Creates a materialized view with query results stored physically
REFRESH MATERIALIZED VIEW view_name	Updates the materialized view data from the base tables
DROP MATERIALIZED VIEW view_name	Deletes the materialized view
WITH NO DATA	Creates the view without populating data immediately
WITH DATA	Creates and populates the view immediately (default)

Key Takeaways

Use CREATE MATERIALIZED VIEW to store query results physically for faster reads.

Materialized views do not update automatically; use REFRESH MATERIALIZED VIEW to update data.

WITH NO DATA creates the view without data; remember to refresh before querying.

Materialized views consume storage space since data is saved on disk.

Use materialized views for expensive queries that do not need real-time data.