Updated on 2024-06-03 GMT+08:00

DROP MATERIALIZED VIEW

Function

Deletes an existing materialized view from the database.

Precautions

The owner of a materialized view, owner of the schema of the materialized view, users granted with the DROP permission on the materialized view, or users granted with the DROP ANY TABLE permission can run the DROP MATERIALIZED VIEW command. By default, the system administrator has the permission to run the command.

Syntax

DROP MATERIALIZED VIEW [ IF EXISTS ] mv_name [, ...] [ CASCADE | RESTRICT ];

Parameter Description

  • IF EXISTS

    Reports a notice instead of an error if the specified materialized view does not exist.

  • mv_name

    Name of the materialized view to be deleted.

  • CASCADE | RESTRICT
    • CASCADE: automatically deletes the objects that depend on a materialized view.
    • RESTRICT: refuses to delete a materialized view if any objects depend on it. This is the default value.

Examples

-- Create a table.
gaussdb=# CREATE TABLE my_table (c1 int, c2 int)
WITH(STORAGE_TYPE=ASTORE);

-- Create a materialized view named my_mv.
gaussdb=# CREATE MATERIALIZED VIEW my_mv AS SELECT * FROM my_table;

-- Delete the materialized view named my_mv.
gaussdb=# DROP MATERIALIZED VIEW my_mv;

-- Delete the table.
gaussdb=# DROP TABLE my_table;