AdvancedAdvanced PostgreSQL

Create a Materialized View

The query

SQL

CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) as revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

Tested against PostgreSQL 16

Note

“Materialized Views STORE the query result physically on disk. They are extremely fast for read-heavy analytical dashboards.

Tables referenced

orders6 cols