I was trying to create a materialized view recently, and got this error:
WARNING: An incrementally maintained materialized view could not be created, reason: Column aliases are not supported. The materialized view created, ***, will be recomputed from scratch for every REFRESH.
My view definition did include some column aliases:
CREATE MATERIALIZED VIEW foo AUTO REFRESH YES AS SELECT trunc(date_col) date, platform, operator, category, game_name, count(*) as count1, sum(bar) as sum1, count(distinct baz) as count2 FROM xxx GROUP BY 1, 2, 3, 4, 5;
so that did seem believable (although a little unreasonable, and not covered in the documented limitations).
I decided to split my view up, so I didn’t have multiple aggregations of the same type, and I could use the generated col names (e.g.
count). I could then have a, non-materialized “super” view, to join them all back together again.
At this point, thanks to some incompetent copy pasta, I discovered that redshift would quite happily create an auto refresh view with a column alias.
Eventually, I realised that the real problem was the
count(distinct), which makes much more sense. You can’t incrementally update it, without tracking all the existing values.
Side note: it is also possible to use `APPROXIMATE COUNT (DISTINCT …), with some caveats