DROP_INDEX(7)

📦
version 18.6 · 20 versions
, in alpine 3.23, alpine 3.24, arch rolling, fedora 43, fedora 44, nixos 26.05, ubuntu resolute, ubuntu stonking

💅 DROP INDEX — the "okay bye bestie" of indexes

postgresql 18.6 docs · DROP INDEX(7) · yes this is the manual page, no I will not be normal about it

✨ NAME ✨

DROP_INDEX — remove an index. that's it. that's the whole vibe. it's giving "we're deleting the thing."

🎀 SYNOPSIS 🎀

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

look at her. so simple. so powerful. slay.

💖 DESCRIPTION 💖

okay so DROP INDEX drops an existing index from the database system. gone. deleted. no more index. ✨

one lil' thing though: to actually run this command you must be the owner of the index. if you're not the owner, bestie, this is not your moment. 💀

🦋 PARAMETERS 🦋

  • CONCURRENTLY — drops the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. lowkey the polite option. a normal DROP INDEX grabs an ACCESS EXCLUSIVE lock on the table, which blocks literally everyone else until the drop finishes. with this option, the command instead just waits until conflicting transactions have completed. patient queen behavior. 👑
⚠️ but babes, there are SEVERAL caveats here and you need to know all of them: - only one index name can be specified, so no comma-list shenanigans - the CASCADE option is not supported with this - (which means an index that supports a UNIQUE or PRIMARY KEY constraint cannot be dropped this way — it's giving "trapped by your own commitments" 😭) - regular DROP INDEX commands can be performed within a transaction block, but DROP INDEX CONCURRENTLY cannot - lastly, indexes on partitioned tables cannot be dropped using this option. no. not happening.

and for temporary tables? DROP INDEX is always non-concurrent, because no other session can access them anyway, and a non-concurrent index drop is cheaper. like why take the scenic route, fr fr. 🚗

  • IF EXISTS — do not throw an error if the index does not exist. instead, a notice is issued in this case. it's the "no worries if not!!" of flags 🫶
  • name — the name (optionally schema-qualified) of an index to remove. yes you can schema-qualify her. she's flexible like that. 🌸
  • CASCADE — automatically drops objects that depend on the index, and in turn all objects that depend on those objects (see Section 5.15). it's a whole domino situation. one falls, they all fall. 💀
  • RESTRICT — refuses to drop the index if any objects depend on it. this is the default. respectful. cautious. boundaries. 🎀

⭐ EXAMPLES ⭐

this command will remove the index title_idx:

DROP INDEX title_idx;

and that's it. she's gone. bye girl 👋💖

🌈 COMPATIBILITY 🌈

DROP INDEX is a PostgreSQL language extension. there are no provisions for indexes in the SQL standard. so like, this is our thing. it's giving "iykyk" ✨

👑 SEE ALSO 👑

  • CREATE INDEX (CREATE_INDEX(7)) — for when you inevitably need her back, bestie. the circle of life. 🦋

mwah 💋 that was PostgreSQL 18.6 docs, dated 2026, and this page is DROP INDEX(7). go forth and drop responsibly. no cap. 💅✨

📦 where this page comes from