How to fix Materialized view has not been populated in PostgreSQL

The immediate fix is to populate the materialized view by running the REFRESH command:

REFRESH MATERIALIZED VIEW your_view_name;

Replace your_view_name with the actual name of your materialized view. This executes the underlying query and stores the results, making the view queryable. The operation will block other transactions from accessing the view until complete.

After refreshing, query the view to confirm it now contains data:

-- Check the row count
SELECT COUNT(*) FROM your_view_name;

-- Or examine some sample data
SELECT * FROM your_view_name LIMIT 10;

If this query succeeds, the view is properly populated and accessible.

For views that are frequently accessed, you can avoid blocking queries during refresh by using the CONCURRENTLY option:

REFRESH MATERIALIZED VIEW CONCURRENTLY your_view_name;

This allows other transactions to continue reading from the view while the refresh happens. However, this requires a unique index on the materialized view:

-- First, create a unique index on the view
CREATE UNIQUE INDEX idx_view_key ON your_view_name (primary_key_column);

-- Then use CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY your_view_name;

Without the unique index, the CONCURRENTLY option will fail.

To prevent this error going forward, populate materialized views immediately upon creation by omitting the WITH NO DATA clause:

-- Good: creates and populates immediately
CREATE MATERIALIZED VIEW my_view AS
SELECT id, name, COUNT(*) as count
FROM users
GROUP BY id, name;

-- Avoid: creates but does not populate
CREATE MATERIALIZED VIEW my_view AS
SELECT id, name, COUNT(*) as count
FROM users
GROUP BY id, name
WITH NO DATA;
-- requires: REFRESH MATERIALIZED VIEW my_view;

Use WITH NO DATA only when you need to define the view structure before the underlying tables are ready, or when the refresh takes too long to block creation.

Since materialized views are static snapshots, they become stale over time. Set up automatic refreshes using the pg_cron extension:

-- Install pg_cron if not already installed
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule daily refresh at 2 AM
SELECT cron.schedule('refresh_my_view', '0 2 * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY my_view');

-- Or hourly refresh
SELECT cron.schedule('refresh_my_view', '0 * * * *', 'REFRESH MATERIALIZED VIEW my_view');

-- List scheduled jobs
SELECT * FROM cron.job;

-- Cancel a scheduled job
SELECT cron.unschedule('refresh_my_view');

Alternatively, use application-level scheduling or trigger refreshes from your application code after data modifications.