DROP_INDEX(7pg13)

💅 DROP_INDEX — the "we're so over you" command

Okay bestie, so this is the DROP INDEX page from the PostgreSQL 13.23 docs (part of the 7pg13 manual set). It's a short one, but it's lowkey iconic, so let's get into it 🫶

---

✨ NAME ✨

DROP_INDEX — remove an index. That's it. That's the whole vibe. Girl's gotta go 💀

---

🌸 SYNOPSIS 🌸

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

Look at that little line of brackets. Every single one is optional, bestie — it's giving choose your own adventure 🦋

---

🎀 DESCRIPTION 🎀

So DROP INDEX literally drops an existing index right out of the database system. Poof. Gone. Not me being dramatic, it's just facts 💅

One thing though — to execute this command you must be the owner of the index. No ownership, no drop. Them's the rules, periodt.

---

👑 PARAMETERS 👑

• CONCURRENTLY — Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. Okay so here's why that matters: a normal DROP INDEX acquires an ACCESS EXCLUSIVE lock on the table, which blocks all other accesses until the index drop can be completed. Kind of rude, honestly 😭 With this option though, the command instead waits until conflicting transactions have completed. So polite, so patient, we stan ✨

Lowkey there are several caveats to be aware of when using this option, and I need you to hear me on these: - Only one index name can be specified - The CASCADE option is not supported - (Thus, an index that supports a UNIQUE or PRIMARY KEY constraint cannot be dropped this way. Sorry bestie 💀) - Regular DROP INDEX commands can be performed within a transaction block, but DROP INDEX CONCURRENTLY cannot - Indexes on partitioned tables cannot be dropped using this option

For temporary tables, DROP INDEX is always non-concurrent, as no other session can access them, and non-concurrent index drop is cheaper. Makes sense — no audience, no need for the fancy footwork 🧚

• IF EXISTS — Do not throw an error if the index does not exist. A notice is issued in this case. So instead of screaming at you, it just kind of quietly notes it. Very demure 🙈
• name — The name (optionally schema-qualified) of an index to remove. That's your target, bestie 🎯
• CASCADE — Automatically drop objects that depend on the index, and in turn all objects that depend on those objects (see Section 5.14). It's the domino effect — one goes, they all go 🌈
• RESTRICT — Refuse to drop the index if any objects depend on it. This is the default. Very "I'm not touching that, that's not my business" energy 😌

---

💖 EXAMPLES 💖

This command will remove the index title_idx:

DROP INDEX title_idx;

Slay. Bye title_idx 🦋

---

🍓 COMPATIBILITY 🍓

DROP INDEX is a PostgreSQL language extension. There are no provisions for indexes in the SQL standard. So like, this is our thing. Main character energy only 👑

---

⭐ SEE ALSO ⭐

• CREATE INDEX (CREATE_INDEX(7)) — the origin story, iykyk

---

💜 That's the whole page, bestie — go forth and drop responsibly 💜

Cute little sign-off from the footer: this is from PostgreSQL 13.23, dated 2025 — so you know it's the good stuff 🎀🔥