3

I have a materialized view based on a table that on which a column type changed. and I'd like to change the column on the materialized view.

I'm aware that a view is based on it's definition but I couldn't find how to updated the select in the definition.

Is the only way to do it to drop the view and re-create it with a new definition?

bl0b
  • 926
  • 3
  • 13
  • 30

1 Answers1

1

It's not what you were hoping to get, but currently the only way to change the query on which a materialized view is based is to drop and recreate it. Still the case in Postgres 10.

ALTER MATERIALIZED VIEW can only change auxiliary properties. You can also change column names, but not data types.

If concurrent access is required and the MV takes a long time to recreate, you might create a new MV under a different name, populate it and use it instead of the old one to keep downtime to a minimum - if that's an option.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228