0

I have accidentally created hundreds of indexes that have double quotes in the name. Is it possible to drop them altogether or change their name?

I have tried to delete them individually with drop index '"thing"somethingelse';, with double quotes "drop index '"thing"somethingelse";, with escaped quotes "drop index '\"thing\"somethingelse"';, without quotes, with square brackets, but none of them work.

Stu
  • 30,392
  • 6
  • 14
  • 33
sryscad
  • 303
  • 2
  • 12
  • how did you create it in the first place – nbk Jan 13 '23 at 20:17
  • @nbk I wrote a function that generated filenames and quoted them. However it quoted them too much and generated the indexes just like that. – sryscad Jan 13 '23 at 20:38
  • You wrote a function to create the indexes. So revise the function to remove them using the *exact same code* the generate the names. **Lesson to Learn:** Never trust untested code especially your own. When writing code to perform such operations always test on a few items made up (like perhaps 5). That way when your code does not work clean-up fairly is easy and once code is updated it can be retested. – Belayer Jan 15 '23 at 20:00
  • @Belayer, thanks, i resorted to using dbeaver in then end. It doesn't make sense to write functions in SQL or the scripting language coming with postgres. The syntax and debugging experience is horrible. It is much easier to use the GUI. – sryscad Jan 17 '23 at 20:02

2 Answers2

2

You need to follow the same rules as with single quotes: double them:

drop index "thing""somethingelse";

would drop an index with the name thing"somethingelse

-3

In Postgres, if an index has double quotes in its name, you need to use the same double quotes when referencing the index in the DROP INDEX statement.

For example, if the index name is "my_index", you would use the following command to drop it:

DROP INDEX "my_index";

It's also possible to use the pgAdmin tool to drop the index, you can navigate to the index you want to drop and then right-click and choose the "Delete/Drop" option.

Please be careful when using the DROP INDEX statement as it will permanently remove the index and any data stored within it. It's recommended to create a backup of your database before performing any destructive operation.

Also, it's important to consider the impact of dropping an index in the performance of your queries. If you drop an index that is frequently used by your queries, the performance of those queries may degrade.

Fravel
  • 70
  • 2
  • 7
  • 1
    `my_index` does not require quotes. Only otherwise illegal names and upper case letters require quoting. See: https://stackoverflow.com/a/20880247/939860 – Erwin Brandstetter Jan 13 '23 at 21:22