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 Postgres clauses on the continuous aggregate 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

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:

  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;
  

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. Effectively the same as timescaledb.compress. Since 2.18.0
timescaledb.compress	TEXT	Disabled	-	Enable compression.
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.compress_orderby	TEXT	Descending order on the time column in table_name.	-	Set the order used by compression. Specified in the same way as the ORDER BY clause in a SELECT query.
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
timescaledb.compress_segmentby	TEXT	No segmentation by column.	-	Set the list of columns used to segment the compressed data. An identifier representing the source of the data such as device_id or tags_id is usually a good candidate.
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.compress/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.