ALTER MATERIALIZED VIEW (Continuous Aggregate)

CommunityCommunity functions are available under Timescale Community Edition. Click to learn more.

Tiger Cloud: Performance, Scale, Enterprise

Self-hosted products

MST

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 TimescaleDB v2.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`	✖	Enable real-time aggregation.
`timescaledb.enable_columnstore`	BOOLEAN	`true`	✖	Since TimescaleDB v2.18.0 Enable columnstore. Effectively the same as `timescaledb.compress`.
`timescaledb.compress`	TEXT	Disabled.	✖	Enable compression.
`timescaledb.orderby`	TEXT	Descending order on the time column in `table_name`.	✖	Since TimescaleDB v2.18.0 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.
`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 segementation by column.	✖	Since TimescaleDB v2.18.0 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.
`timescaledb.compress_segmentby`	TEXT	No segementation 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.enable_cagg_window_functions`	BOOLEAN	`false`	✖	EXPERIMENTAL: enable window functions on continuous aggregates. Support is experimental, as there is a risk of data inconsistency. For example, in backfill scenarios, buckets could be missed.
`timescaledb.chunk_interval` (formerly `timescaledb.chunk_time_interval`)	INTERVAL	10x the original hypertable.	✖	Set the chunk interval. Renamed in TimescaleDB V2.20.

ALTER MATERIALIZED VIEW (Continuous Aggregate)

Samples

Arguments

Related Content