Skip to main content
You use the ALTER MATERIALIZED VIEW statement to modify some of the WITH clause options for a view. You can only set the continuous and create_group_indexes options when you create a . ALTER MATERIALIZED VIEW also supports the following clauses on the view:
  • RENAME TO: rename the view
  • RENAME [COLUMN]: rename the column
  • SET SCHEMA: set the new schema for the view
  • SET TABLESPACE: move the materialization of the view to the new tablespace
  • OWNER TO: set a new owner for the view

​
Samples

  • Enable real-time aggregates for a : 
    ALTER MATERIALIZED VIEW contagg_view SET (timescaledb.materialized_only = false);
  • Enable hypercore for a : Since 2.18.0 
     ALTER MATERIALIZED VIEW contagg_view SET (
  timescaledb.enable_columnstore = true,
  timescaledb.segmentby = 'symbol' );
  • Rename a column for a : 
    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> [, ... ] )
NameTypeDefaultRequiredDescription
view_nameTEXT-✔The name of the view to be altered.
timescaledb.materialized_onlyBOOLEANtrue-Enable real-time aggregation.
timescaledb.enable_columnstoreBOOLEANtrue-Enable . Effectively the same as timescaledb.compress. Since 2.18.0
timescaledb.compressTEXTDisabled-Enable compression.
timescaledb.orderbyTEXTDescending order on the time column in table_name.-Set the order in which items are used in the . Specified in the same way as an ORDER BY clause in a SELECT query. Since 2.18.0
timescaledb.compress_orderbyTEXTDescending 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.segmentbyTEXTNo segementation by column.-Set the list of columns used to segment data in the 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_segmentbyTEXTNo 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_nameTEXT--Set the name of the column to order by or segment by.
timescaledb.compress_chunk_time_intervalTEXT--Reduce the total number of compressed/ s for table. If you set compress_chunk_time_interval, compressed/ s are merged with the previous adjacent within chunk_time_interval whenever possible. These s are irreversibly merged. If you call to decompress/convert_to_rowstore, merged s 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_functionsBOOLEANfalse-EXPERIMENTAL: enable window functions on s. 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)INTERVAL10x the original .-Set the interval. Renamed in V2.20.