ALTER MATERIALIZED VIEW

Description

ALTER MATERIALIZED VIEW modifies the properties of a materialized view. This syntax is supported only in clusters of version 8.2.1.220 or later.

Precautions

To use ALTER MATERIALIZED VIEW, ensure that enable_matview is set to on.

Syntax

• Set whether to enable query rewrite for materialized views.

  1 2	ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name } [ ENABLE | DISABLE ] QUERY REWRITE;

• Modify the refresh method of a materialized view or update a materialized view.

  1 2	ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name } REFRESH [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ] [ COMPLETE ] [ ON DEMAND ] [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ];

  • REFRESH ON DEMAND indicates that the data is manually refreshed as required.
  • START WITH specifies the first refresh time.
  • EVERY specifies the refresh interval. The value can be MONTH, DAY, HOUR, MINUTE, or SECOND.
• Change how materialized views refresh asynchronously in the background. This change applies only to materialized views that refresh this way. This syntax is supported only in clusters of version 9.1.0.200 or later. For details, see REFRESH MATERIALIZED VIEW.

  1 2	ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name } REFRESH [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ;

• Change the owner of the materialized view.

  1 2	ALTER MATERIALIZED VIEW { materialized_view_name } OWNER TO new_owner;

• Sett table properties of the materialized view. This syntax is supported only in clusters of version 9.1.0.200 or later.

  1 2 3

  ALTER MATERIALIZED VIEW { materialized_view_name } SET ( {storage_parameter = value} [, ... ] ) | RESET ( storage_parameter [, ... ] )

• Rename a materialized view. Materialized views cannot be renamed across schemas. When renaming a materialized view, you can specify a schema name for materialized_view_name but cannot specify a schema name for new_materialized_view_name.

  1 2	ALTER MATERIALIZED VIEW [ IF EXISTS ] materialized_view_name RENAME TO new_materialized_view_name;

Parameter Description

Examples

Enable the materialized view function.

1

SET enable_matview = on;

Create a base table and insert data into the base table.

1
2
3
4
5

DROP TABLE IF EXISTS t1 CASCADE;
CREATE TABLE t1 (a int, b int) 
WITH (ORIENTATION = COLUMN )
DISTRIBUTE BY HASH(a);
INSERT INTO t1 SELECT x,x FROM generate_series(1,10) x;

Create a materialized view with a specified column-store method:

1

CREATE MATERIALIZED  VIEW mv1 WITH(orientation = column, enable_foreign_table_query_rewrite = false ) AS SELECT * FROM t1;

Enable query rewrite for a materialized view.

1

ALTER MATERIALIZED VIEW mv1 ENABLE QUERY REWRITE;

Modify the table properties of a materialized view.

1
2
3

ALTER MATERIALIZED VIEW mv1 SET (force_rewrite_timeout=100);
ALTER MATERIALIZED VIEW mv1 SET (mv_pck_column='b');
ALTER MATERIALIZED VIEW mv1 SET (enable_foreign_table_query_rewrite = true);

Change the refresh time of the materialized view.

1

ALTER MATERIALIZED VIEW mv1 REFRESH START WITH('2025-01-01 15:15:15'::timestamptz) EVERY (interval '60 s');

Modify the name of the materialized view.

1

ALTER MATERIALIZED VIEW my_schema.mv1 RENAME TO new_mv;

Helpful Links

CREATE MATERIALIZED VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW