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.

CodePudding user response：

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

CodePudding user response：

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.