ALTER MATERIALIZED VIEW (continuous aggregate)

Change an existing continuous aggregate

Since 1.3.0

You use the ALTER MATERIALIZED VIEW statement to modify some of the WITH clause options for a continuous aggregate view. You can only set the continuous and create_group_indexes options when you create a continuous aggregate. ALTER MATERIALIZED VIEW also supports the following clauses on the continuous aggregate view. The PostgreSQL clauses 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 <name> <type> GENERATED ALWAYS AS (<expression>) 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

 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 <view_name> SET ( timescaledb.<argument> =  <value> [, ... ] )

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/convert_to_rowstore, 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.