Skip to main content

ALTER MATERIALIZED VIEW

Redefines a materialized view.

Syntax​

ALTER MATERIALIZED VIEW [ IF EXISTS ] <name> <action> [, ... ]
ALTER MATERIALIZED VIEW [ IF EXISTS ] <name>
    RENAME [ COLUMN ] <column_name> TO <new_column_name>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <name>
    RENAME TO <new_name>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <name>
    SET SCHEMA <new_schema>

where <action> is one of:

    ALTER [ COLUMN ] <column_name> SET STATISTICS <integer>
    ALTER [ COLUMN ] <column_name> SET ( <attribute_option> = <value> [, ... ] )
    ALTER [ COLUMN ] <column_name> RESET ( <attribute_option> [, ... ] )
    OWNER TO { <new_owner> | CURRENT_USER | SESSION_USER }

Description​

You can run ALTER MATERIALIZED VIEW to change the auxiliary properties of the materialized view that you own.

To change the schema of a materialized view, you must have the CREATE privilege on the new schema.

To change the owner of a materialized view, you must have the direct or indirect membership in the new owning role. The new owning role must have the CREATE privilege on the schema to which the materialized view belongs.

The statement subforms and actions available for ALTER MATERIALIZED VIEW are a subset of those available for ALTER TABLE, and have the same meaning when used for materialized views. See the descriptions for ALTER TABLE for details.

Parameters​

  • IF EXISTS

    If you include IF EXISTS in your command, no error will be reported if the specified view does not exist. A notice will be issued instead.

  • <name>

    The name of the materialized view. You can specify the name with the schema qualification.

  • <action>

    The action to perform. For details, see "Parameters" in CREATE TABLE.

  • <column_name>

    The name of a new or existing column.

  • <new_column_name>

    The new name of the column.

  • <new_owner>

    The DW user name of the new owner of the materialized view.

  • <new_name>

    The new name of the materialized view.

  • <new_schema>

    The new schema to which the materialized view belongs.

Examples​

Rename materialized view generalmv to general_mv:

ALTER MATERIALIZED VIEW generalmv RENAME TO general_mv;

Alter the owner of materialized view generalmv:

ALTER MATERIALIZED VIEW generalmv OWNER TO relyt;

SQL standard compatibility​

ALTER MATERIALIZED VIEW is a Relyt extension to the SQL standard.