PostgreSQL DROP INDEX statement

Summary: in this tutorial, you’ll learn how to delete an index using the PostgreSQL DROP INDEX statement.

Introduction to PostgreSQL DROP INDEX statement #

The DROP INDEX statement allows you to drop an existing index from a database.

Here’s the basic syntax of the DROP INDEX statement:

DROP INDEX [CONCURRENTLY] [IF EXISTS] index_name
[CASCADE | RESTRICT]Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the index name you want to remove after the DROP INDEX keywords.
Second, use the CONCURRENTLY option to delete an index without locking out concurrent selects, inserts, updates, and deletes on the table.
Third, use the IF EXISTS to delete the index only if it exists conditionally. You’ll encounter an error if you attempt to delete a non-existing index without the IF EXISTS option.
Finally, use the CASCADE option to automatically drop the objects that depend on the index and, in turn, all objects that depend on those objects. The RESTRICT option rejects the index removal if the index has any dependent objects. The RESTRICT option is the default.

First, create an index on the products table:

CREATE INDEX products_safety_stock_idx
ON products(safety_stock);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, drop the index with the name products_safety_stock_idx:

DROP INDEX products_safety_stock_idx;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Was this tutorial helpful ?