ALTER MATERIALIZED VIEW

Function

ALTER MATERIALIZED VIEW changes multiple auxiliary attributes of an existing materialized view.

Statements and actions that can be used for ALTER MATERIALIZED VIEW are a subset of ALTER TABLE and have the same meaning when used for materialized views. For details, see ALTER TABLE.

Precautions

• Only the owner of a materialized view or a system administrator has the ALTER TMATERIALIZED VIEW permission.
• The materialized view structure cannot be modified.

Syntax

• Modify the definition of the materialized view.

  ALTER 
  MATERIALIZED VIEW 
  [ IF EXISTS ] mv_name
      action [, ... ];
  ;
  

• Modify the column of a materialized view.

  ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name
      RENAME [ COLUMN ] column_name TO new_column_name;
  

• Rename a materialized view.

  ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name
  
      RENAME TO new_name;
  

• Set the schema of a materialized view.

  ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name 
      SET SCHEMA new_schema;
  

• Set the tablespace of a materialized view.

  ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name
      SET TABLESPACE new_tablespace;
  

• There are several clauses of action:

  ALTER [ COLUMN ] column_name
  
  SET STATISTICS integer
  ALTER [ COLUMN ] column_name 
      RESET ( attribute_option [, ... ] )
  ALTER [ COLUMN ] column_name 
      SET ( attribute_option = value [, ... ] )
  ALTER [ COLUMN ] column_name 
      SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
  CLUSTER ON index_name
  SET WITHOUT CLUSTER
  SET ( storage_parameter = value [, ... ] )
  RESET ( storage_parameter [, ... ] )
  

Parameter Description

• mv_name

  Specifies the name of an existing materialized view, which can be schema-qualified.

  Value range: a string. It must comply with the naming convention.

• column_name

  Specifies the name of a new or existing column.

  Value range: a string. It must comply with the naming convention.

• new_column_name

  Specifies the new name of an existing column.

• new_owner

  Specifies the user name of the new owner of a materialized view.

• new_name

  Specifies the new name of a materialized view.

• new_schema

  Specifies the new schema of a materialized view.

Examples

-- Rename the materialized view foo to bar.
postgres=# ALTER MATERIALIZED VIEW foo RENAME TO bar;

Helpful Links

CREATE MATERIALIZED VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW, and ALTER TABLE