Cannot drop index in postgres because it does not exists

user_3579955 0
Viewed 3604

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

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.

Below query is still giving 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';

gives the below output

users_dept_idx, i, my_schema

When I execute the below query

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 due to this index which I no longer want this.

database-indexes postgresql
1 Answers

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"
user_35799550
Related