DROP INDEX

Description

Drops an index.

Precautions

Only the index owner, owner of the schema where the index resides, a user who has the INDEX permission on the table where the index resides, or a user who has the DROP ANY INDEX permission can run the DROP INDEX command. When the separation of duties is disabled, the system administrator has this permission by default.
For a global temporary table, if a session has initialized a global temporary table object (including creating a global temporary table and inserting data into the global temporary table for the first time), other sessions cannot delete indexes from the table.

Syntax

DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] 
    index_name [, ...] [ CASCADE | RESTRICT];

DROP INDEX index_name [, ...] [CASCADE | RESTRICT] ON table_name [algorithm_option | lock_option];

Parameters

CONCURRENTLY
Deletes an index without locking it. When an index is deleted, other statements cannot access the table on which the index depends. With this option, the statement does not lock the table during index deletion.

This parameter allows only one index name and does not support CASCADE.

The DROP INDEX statement can be run within a transaction, but DROP INDEX CONCURRENTLY cannot.
IF EXISTS
Reports a notice instead of an error if the specified index does not exist.
index_name
Specifies the index name to be deleted.

Value range: an existing index
CASCADE | RESTRICT
- CASCADE: Dropping the index automatically removes dependent objects.
- RESTRICT: The database refuses to drop the index if any objects depend on it. This is the default action.

table_name
Name of the table.
algorithm_option
For details, see CREATE INDEX.
lock_option
For details, see CREATE INDEX.

Examples

-- Create a table.
m_db=# CREATE TABLE test1_index (id INT, name VARCHAR(20));
m_db=# CREATE INDEX idx_test1 on test1_index (id);

-- Delete an index.
m_db=# DROP INDEX IF EXISTS idx_test1 CASCADE;

-- Drop the table.
m_db=# DROP TABLE test1_index;