--- title: ALTER MATERIALIZED VIEW (continuous aggregate) | Tiger Data Docs description: Change an existing continuous aggregate --- Since [1.3.0](https://github.com/timescale/timescaledb/releases/tag/1.3.0) You use the `ALTER MATERIALIZED VIEW` statement to modify some of the `WITH` clause [options](/reference/timescaledb/continuous-aggregates/create_materialized_view#arguments/index.md) for a continuous aggregate view. You can only set the `continuous` and `create_group_indexes` options when you [create a continuous aggregate](/reference/timescaledb/continuous-aggregates/create_materialized_view#arguments/index.md). `ALTER MATERIALIZED VIEW` also supports the following clauses on the continuous aggregate view. The [PostgreSQL clauses](https://www.postgresql.org/docs/current/sql-alterview.html) behave as they do for a standard view: - `RENAME TO`: rename the continuous aggregate view - `RENAME [COLUMN]`: rename the continuous aggregate column - `SET SCHEMA`: set the new schema for the continuous aggregate view - `SET TABLESPACE`: move the materialization of the continuous aggregate view to the new tablespace - `OWNER TO`: set a new owner for the continuous aggregate view - `ADD COLUMN GENERATED ALWAYS AS () STORED`: add a new column to the continuous aggregate without recreating it. The expression can be a new aggregate, or a column that already appears in the `GROUP BY` clause of the continuous aggregate definition. New data populates the column as the continuous aggregate refreshes going forward; existing rows remain `NULL` until you force a refresh over the historical range to backfill them. ## Samples - Enable real-time aggregates for a continuous aggregate: ``` ALTER MATERIALIZED VIEW contagg_view SET (timescaledb.materialized_only = false); ``` - Enable hypercore for a continuous aggregate: Community Since [2.18.0](https://github.com/timescale/timescaledb/releases/tag/2.18.0) ``` ALTER MATERIALIZED VIEW contagg_view SET ( timescaledb.enable_columnstore = true, timescaledb.segmentby = 'symbol' ); ``` - Rename a column for a continuous aggregate: ``` ALTER MATERIALIZED VIEW contagg_view RENAME COLUMN old_name TO new_name; ``` - Add a new aggregate to a continuous aggregate without recreating it: ``` ALTER MATERIALIZED VIEW contagg_view ADD COLUMN max_temp double precision GENERATED ALWAYS AS (max(temperature)) STORED; ``` You can also add a column that already appears in the `GROUP BY` clause of the continuous aggregate definition, even when it is not an aggregate: ``` ALTER MATERIALIZED VIEW contagg_view ADD COLUMN device_id integer GENERATED ALWAYS AS (device_id) STORED; ``` In both cases, the new column is populated for new data as the continuous aggregate refreshes going forward. Existing rows remain `NULL` until you force a refresh over the historical range to backfill them: ``` CALL refresh_continuous_aggregate('contagg_view', '2020-01-01', '2020-02-01', force => true); ``` ## Arguments The syntax is: ``` ALTER MATERIALIZED VIEW SET ( timescaledb. = [, ... ] ) ``` | Name | Type | Default | Required | Description | | ------------------------------------------------------------------------- | -------- | ---------------------------------------------------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `view_name` | TEXT | - | ✔ | The name of the continuous aggregate view to be altered. | | `timescaledb.materialized_only` | BOOLEAN | `true` | - | Return only materialized data when querying the continuous aggregate view. Set to `false` to enable real-time aggregation. | | `timescaledb.enable_columnstore` | BOOLEAN | `true` | - | Enable columnstore. Since 2.18.0 | | `timescaledb.orderby` | TEXT | Descending order on the time column in `table_name`. | - | Set the order in which items are used in the columnstore. Specified in the same way as an `ORDER BY` clause in a `SELECT` query. Since 2.18.0 | | `timescaledb.segmentby` | TEXT | No segmentation by column. | - | Set the list of columns used to segment data in the columnstore for `table`. An identifier representing the source of the data such as `device_id` or `tags_id` is usually a good candidate. Since 2.18.0 | | `column_name` | TEXT | - | - | Set the name of the column to order by or segment by. | | `timescaledb.compress_chunk_time_interval` | TEXT | - | - | Reduce the total number of compressed/columnstore chunks for `table`. If you set `compress_chunk_time_interval`, compressed/columnstore chunks are merged with the previous adjacent chunk within `chunk_time_interval` whenever possible. These chunks are irreversibly merged. If you call to [decompress](/reference/timescaledb/hypercore/convert_to_rowstore/index.md)/[convert\_to\_rowstore](/reference/timescaledb/hypercore/convert_to_rowstore/index.md), merged chunks are not split up. You can call `compress_chunk_time_interval` independently of other compression settings; `timescaledb.enable_columnstore` is not required. | | `timescaledb.chunk_interval` (formerly `timescaledb.chunk_time_interval`) | INTERVAL | 10x the original hypertable. | - | Set the chunk interval. Renamed in TimescaleDB V2.20. | ## Returns For standard `ALTER MATERIALIZED VIEW` return behavior, see the [PostgreSQL ALTER MATERIALIZED VIEW documentation](https://www.postgresql.org/docs/current/sql-alterview.html).