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.

Are you sure you are connected to the same database throughout all of these operations? Why are you using python rather than psql for what seems to be an interactive use case? — jjanes
– jjanes, Commented Mar 21, 2020 at 15:57
Is all your input and output copied verbatim, or have you tried to manually anonymize them? — jjanes
– jjanes, Commented Mar 21, 2020 at 16:00
you've got to make sure you are on the right DB context, it looks like your alchemy run is happening on a db which doesn't hold the index, ,mostly I have seen this when folks do it again the default, i.e. postgres db. — Raj Verma
– Raj Verma, Commented Mar 21, 2020 at 16:27
I'm using python module sqlachemy and the above queries are executed using 'engine.execute()'. I am sure that I'm connecting to the same db as there are no other dbs — newbie
– newbie, Commented Mar 21, 2020 at 16:55
I have the same. When deleting it does not exists, when listing or creating -> already existing. — niedomnie
– niedomnie, Commented Apr 1, 2021 at 12:55

newbie · Accepted Answer · 2021-04-01 14:28:02Z

10

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"

answered Apr 1, 2021 at 14:28

newbie

1,4407 gold badges24 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

okharch · Accepted Answer · 2022-11-29 12:31:12Z

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

answered Nov 29, 2022 at 12:31

okharch

4654 silver badges11 bronze badges

Collectives™ on Stack Overflow

Cannot drop index in postgres because it does not exist

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related