DROP INDEX

Function

DROP INDEX deletes an index.

Precautions

Only the owner of an index or a system administrator has the DROP INDEX permission.

Syntax

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

Parameter Description

• CONCURRENTLY

  Deletes an index without locking it. A normal DROP INDEX acquires exclusive lock on the table on which the index depends, blocking other accesses until the index drop can be completed. 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: automatically deletes the objects that depend on the index.
  • RESTRICT: refuses to delete the index if any objects depend on it. This is the default action.

Examples

See Examples in CREATE INDEX.

Helpful Links

ALTER INDEX and CREATE INDEX