Reputation: 4541
I am needing to add a new column to an existing materialized view in Postgres.
According to this documentation: https://www.postgresql.org/docs/9.3/sql-altermaterializedview.html
It says these are the options for columns:
ALTER [ COLUMN ] column_name SET STATISTICS integer
ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
but there are no syntax examples for adding a new column, even though it says column_name is "the name of a new or existing column"
... or should I use SET SCHEMA new_schema
?
Upvotes: 4
Views: 10608
Reputation: 81
As I am having the same issue, I thought I would share a way that could be beneficial if like us you are using materialized views in production environment and it's not practical to have it unavailable for a long period of time.
As Jeremy mentioned, there is no other way than dropping the existing view and recreate it. However, creating a materialized view can take a significant amount of time depending on the complexity of the underlying query. One way to improve this is to create the materialized view with a different temporary name, drop the old one and rename the temporary one to the final name.
Use:
CREATE MATERIALIZED VIEW myViewTmp AS (SELECT ...);
DROP MATERIALIZED VIEW myView;
ALTER MATERIALIZED VIEW myViewTmp RENAME TO myView;
DROP & ALTER should only take a couple of 100ms which can be acceptable. If not, you will have to be smarter in your code to have it query a new version of the materialized view on deployment or something similar.
Upvotes: 8
Reputation: 6723
Setting the schema is just for moving the view to a different schema, it won't help in this case.
Your only option is to drop and re-create the materialized view, unfortunately.
Upvotes: 5