CODEWITHSUNDEEP
postgresql
Doug Fir
Doug Fir

Reputation: 21204

Is it possible to create a new index on a Materialized View of the form 'create materialized view as'?

(show server_version; 11.5)

I have a Materialized View:

drop materialized view if exists reporting.ecom_channel;
create materialized view reporting.ecom_channel  as


select 
    s.date,
    s.user_type,
    s.channel_grouping,
    s.device_category,
    sum(s.sessions) as sessions,
    count(distinct s.dimension2) as daily_users,
    sum(s.transactions) as transactions,
    sum(s.transaction_revenue) as revenue
from ecom.sessions s
group by 1,2,3,4

There is no id field on that query. For my regular tables, those are specified 'traditionally' with 'create table tablename .... id serial, field1 int, etc'

Since I created my materialized view as being based on the results of a query as opposed to defining each field name during creation, how can I add a id?

The reason that I'm doing this is because when I tried:

refresh materialized view concurrently reporting.ecom_channel;

I got this error:

ERROR: Cannot refresh materialized view "reporting.channel" concurrently. Hint: Create a unique index with no WHERE clause on one or more columns of the materialized view

How can I add an ID within the context of the above? With 

create materialized view reporting.ecom_channel  as
select 
    s.date,
    s.user_type,
    ...

Upvotes: 9

Views: 33037

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246188

That unique index could be

CREATE UNIQUE INDEX ON reporting.ecom_channel (
   date,
   user_type,
   channel_grouping,
   device_category
);

This combination must be unique based on the query that defines the view.

Upvotes: 12

Related Questions