0

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?

DorothyL
  • 3
  • 1

1 Answers1

-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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • The thing is, I had dropped the schema first and it did work, but then the schema somehow was shown again in the navigation panel; I had dropped it several times more to ensure if I had dropped it, but then it says that schema doesn't exist. Only after that I had tried deleting pg_namespace. I didn't use uppercase character, and the single quote is just to bound the name as schema - in the real query I ran I didn't use any quotes (single/double). Anyway, thanks for the solution, so I need to delete the whole database now. – DorothyL Dec 14 '22 at 04:16
  • Well, we won't be able to figure out exactly what the problem was; you have successfully compromised the evidence. Perhaps some software kept creating the schema automatically? – Laurenz Albe Dec 14 '22 at 04:20
  • Indeed, I think I tried too much things before asking so the exact problem is now a mystery. I suppose maybe there was some softward creating the schema, but I didn't have much softwares open, so not sure if that was the case - but I would still thank you on confirming that I am left with no other choice. – DorothyL Dec 14 '22 at 04:56