Add a new column to a Postgres materialized view

user_214935 0
Viewed 3363

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?

materialized-views postgresql
2 Answers

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.

user_8956400

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.

user_163432140
Related