Creating and Using Materialized Views

CREATE MATERIALIZED VIEW customer_stats AS
SELECT customer_id, count(*) AS orders, sum(total_cents) AS revenue
  FROM orders GROUP BY customer_id
WITH DATA;

CREATE MATERIALIZED VIEW v WITH (fillfactor = 100) AS SELECT ... WITH DATA;

CREATE MATERIALIZED VIEW v AS SELECT ... WITH NO DATA;
REFRESH MATERIALIZED VIEW v;

SELECT * FROM customer_stats WHERE orders > 10;

REFRESH MATERIALIZED VIEW customer_stats;

CREATE UNIQUE INDEX customer_stats_pk ON customer_stats(customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_stats;

CREATE INDEX customer_stats_rev ON customer_stats(revenue DESC);

\dm
SELECT matviewname, ispopulated, definition
  FROM pg_matviews;

DROP MATERIALIZED VIEW IF EXISTS customer_stats;