Cannot drop schema because schema does not exist but it exists in pg_namespace

Question

I am having a problem with dropping schema to create it again. When I run:

drop schema 'schema_name' cascade

I get the error message saying "schema does not exist".

But when I search pg_namespace, the 'schema_name' is still there; even with \dn in SQL shell, the 'schema_name' still exists.

I tried to run:

delete
from pg_namespace pn
--where nspname = 'schema_name'

I had no rows returned. When I ran again, I found that the row is deleted (I ran the SELECT query to check), but again the row is alive with another oid. So when I try to create a new schema with the same namespace, I get the error message saying that the duplicate key value is violating the condition of "pg_namespace_nspname_index": (nspname)=(schema_name) key already exists.

So I cannot create the new schema with the same name, and in the navigator panel I can still see the schema_name schema.

How can I permanently delete/drop this schema correctly?

Answer 1

Congratulations. By messing with the catalog tables, you have probably destroyed this database beyond recovery. You cannot drop a schema by deleting a row from pg_namespace. This is the time to get your backup.

Before you did that, the problem was probably simple enough, like an uppercase character, and you forgot the double quotes.