DROP INDEX

This page documents the preview version (v2.21). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

Synopsis

Use the DROP INDEX statement to remove an index from the database.

Syntax

drop_index ::= DROP INDEX [ IF EXISTS ] index_name 
               [ CASCADE | RESTRICT ]

drop_index

DROPINDEXIFEXISTSindex_nameCASCADERESTRICT

Semantics

if_exists

Under normal operation, an error is raised if the index does not exist. Adding IF EXISTS will quietly ignore any non-existent indexes specified.

index_name

Specify the name of the index to be dropped. Objects associated with the index will be invalidated after the DROP INDEX statement is completed.

RESTRICT / CASCADE

RESTRICT (the default) will not drop the index if any objects depend on it.

CASCADE will drop any objects that transitively depend on the index.

Example

Create a table with an index:

CREATE TABLE t1(id BIGSERIAL PRIMARY KEY, v TEXT);
CREATE INDEX i1 ON t1(v);

Verify the index was created:

\d t1

                            Table "public.t1"
 Column |  Type  | Collation | Nullable |            Default             
--------+--------+-----------+----------+--------------------------------
 id     | bigint |           | not null | nextval('t1_id_seq'::regclass)
 v      | text   |           |          | 
Indexes:
    "t1_pkey" PRIMARY KEY, lsm (id HASH)
    "i1" lsm (v HASH)

Drop the index:

DROP INDEX i1;

Use the \d t1 meta-command to verify that the index no longer exists.

\d t1

                            Table "public.t1"
 Column |  Type  | Collation | Nullable |            Default             
--------+--------+-----------+----------+--------------------------------
 id     | bigint |           | not null | nextval('t1_id_seq'::regclass)
 v      | text   |           |          | 
Indexes:
    "t1_pkey" PRIMARY KEY, lsm (id HASH)

See also