Cannot drop index in postgres because it does not exist

Question

In a postgres database, I have a unique constraint and two unique indexes created for the same. I deleted the constraint using the query below:

alter table my_schema.users drop constraint users_dept_uk

It has dropped the constraint and one index but there was a second index which still exists.

The follwoing query is still telling me the index exists:

SELECT r.relname, r.relkind, n.nspname
FROM pg_class r INNER JOIN pg_namespace n ON r.relnamespace = n.oid
WHERE r.relname = 'users_dept_idx';

It gives the following output:

users_dept_idx, i, my_schema

When I execute the query below:

drop index my_schema.users_dept_idx

I am getting the error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) index "users_dept_idx" does not exist

What am I missing here? Not able to delete it and not able to insert data because of this index which I no longer want.

Answer 1

It is weird that the index name needs quotation around it. Below command worked absolutely fine and dropped the index as well

drop index my_schema."users_dept_idx"

Answer 2

I had a similar problem. It turns out that when you create an index it is created in the same schema as the underlying table. In this case, you don't have to use the "schema" part of the name. When you drop it you have to use the full name of the index:

create index if not exists ah_login_minute on api.acc_history(login,updated_minute);
drop index if exists ah_login_minute; -- this fails, you have to use full name
drop index if exists api.ah_login_minute; -- this works