ALTER MATERIALIZED VIEW (hypercore)

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

ALTER MATERIALIZED VIEW statement can be used to modify some of the WITH clause options for the continuous aggregate view. ALTER MATERIALIZED VIEW statement also supports the following PostgreSQL clauses on the continuous aggregate view:

RENAME TO clause to rename the continuous aggregate view
RENAME [COLUMN] clause to rename the continuous aggregate column
SET SCHEMA clause to set the new schema for the continuous aggregate view
SET TABLESPACE clause to move the materialization of the continuous aggregate view to the new tablespace
OWNER TO clause to set new owner for the continuous aggregate view

Since TimescaleDB v2.18.0

Samples

Disable real-time aggregates for a continuous aggregate:


ALTER MATERIALIZED VIEW contagg_view SET (timescaledb.materialized_only);

Enable hypercore for a continuous aggregate:


ALTER MATERIALIZED VIEW assets_candlestick_daily 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;

The only options that currently can be modified with ALTER MATERIALIZED VIEW are materialized_only and compress. The other options continuous and create_group_indexes can only be set when creating the continuous aggregate.

Arguments

The syntax is:


ALTER MATERIALIZED VIEW <view_name> SET (timescaledb.enable_columnstore,
   timescaledb.materialized_only = 'true' | 'false',
   timescaledb.orderby = '<column_name> [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]',
   timescaledb.segmentby = '<column_name> [, ...]',
   timescaledb.compress_chunk_time_interval='interval',
);

Name	Type	Default	Required	Description
`view_name`	TEXT	-	✖	The materialized view to enable columstore for.
`timescaledb.enable_columnstore`	BOOLEAN	`true`	✖	Enable columnstore.
`timescaledb.materialized_only`	BOOLEAN	`true`	✖	Enable and disable real time aggregation
`timescaledb.orderby`	TEXT	Descending order on the time column in `table_name`.	✖	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.segmentby`	TEXT	No segementation 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.
`column_name`	TEXT	-	✖	The name of the column to `orderby` or `segmentby`.
`timescaledb.compress_chunk_time_interval`	TEXT	-	✖	EXPERIMENTAL: reduce the total number of chunks in the columnstore for `table`. If you set `compress_chunk_time_interval`, chunks added to the columnstore are merged with the previous adjacent chunk within `chunk_time_interval` whenever possible. These chunks are irreversibly merged. If you call [convert_to_rowstore][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.

ALTER MATERIALIZED VIEW (hypercore)

Samples

Arguments

Related Content