DROP INDEX

DROP INDEX removes an index from a materialized view. (Non-materialized views do not have any indexes.)

Syntax

Field	Use
IF EXISTS	Do not return an error if the named index doesn’t exist.
index_name	The name of the index you want to remove.
CASCADE	Remove the index and its dependent objects.
RESTRICT	Remove the index. (Default.)

Note: Since indexes cannot currently have dependent objects, DROP INDEX, DROP INDEX RESTRICT, and DROP INDEX CASCADE all do the same thing.

Details

Indexes and materialized views

Primary indexes

By default, materialized views only have one index, which we call the “primary index”, and which stores the result set of the materialized view’s embedded SELECT statement. You can identify the primary index by its name, which follows the format <view name>_primary_idx.

This primary index doesn’t follow the constraints for typical for primary indexes in traditional databases; it is simply the first index created on a view. Please keep in mind the following constraints:

If you remove the primary index, you will not be able to query any columns that are not included in another index for the view.
If the primary index is the only index on the view, you will not be able to query any columns at all and the materialized view will become a non-materialized view. For more details on non-materialized views, see CREATE VIEW.

If you remove the primary index and want to recreate it:

Retrieve the view’s embedded SELECT statement with SHOW CREATE VIEW.
Re-create the view as a materialized view with CREATE OR REPLACE MATERIALIZED VIEW.

Alternatively, you can also CREATE INDEX to materialize any view.

Examples

Remove an index

SHOW VIEWS;

+-----------------------------------+
| VIEWS                             |
|-----------------------------------|
| ...                               |
| q01                               |
+-----------------------------------+

SHOW INDEXES ON q01;

+--------------------------------+------------------------+-----
| name                           | on                     | ...
|--------------------------------+------------------------+----
| materialize.public.q01_geo_idx | materialize.public.q01 | ...
+--------------------------------+------------------------+-----

You can use the unqualified index name (q01_geo_idx) rather the fully qualified name (materialize.public.q01_geo_idx).

You can remove an index with any of the following commands:

```
DROP INDEX q01_geo_idx;
```
```
DROP INDEX q01_geo_idx RESTRICT;
```
```
DROP INDEX q01_geo_idx CASCADE;
```

Do not issue an error if attempting to remove a nonexistent index

DROP INDEX IF EXISTS q01_geo_idx;