materialized_view.md

Understand error on materized view refresh concurrently.

ERROR:  cannot refresh materialized view "public.mvw_prod" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

Works

create materialized view mvw_prod as select productid, productname, supplierid from products join categories using(categoryid) where productid>5;

create unique index uidx_product_name on mvw_prod(productname);

refresh materialized view concurrently mvw_prod;

Also works

create materialized view mvw_prod as select productid, productname, supplierid from products join categories using(categoryid) where productid>5;

create unique index uidx_product_name on mvw_prod(productname) where 1=1; -- so where is allowed

refresh materialized view concurrently mvw_prod;

Does not work

create materialized view mvw_prod as select productid, productname, supplierid from products join categories using(categoryid) where productid>5;

create unique index uidx_product_name on mvw_prod(productname); 
-- where is not allowed on column of materialized view(mvw) as it filters out rows of mvw, but we need to have unique index on
all rows of mvw..

refresh materialized view concurrently mvw_prod;