postgresql - Drop schema says schema does not exist

Question

I want to remove a user and I get this message.

postgres=# drop user user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  owner of schema schema1
postgres=# 

So, I tried dropping the schema and get this message:

postgres=# drop schema schema1 cascade;
ERROR:  schema "schema1" does not exist

But, the schema exists!

postgres=# \dn
     List of schemas
     Name      |  Owner   
---------------+----------
 schema1       | user1
 public        | postgres
(2 rows)

Answer 1

Assuming you do both commands in the same database, the most likely explanation is that "schema1" isn't called what you think it is. There will be a space after it or some such.

Try something like the following to see if there is anything odd there.

SELECT ':' || nspname || ':' FROM pg_namespace;

EDIT: corrected information from poster

The results of your query didn't shown anything different than the original name. I created the schema name in upper case and is shown in upper case in output of \dn

But that's not what you showed, is it?

postgres=# \dn List of schemas Name | Owner
---------------+---------- schema1 | user1 public | postgres (2 rows)

So - if it is actually upper-case that means you forced it to upper-case by double-quoting it on creation.

PostgreSQL will normally fold identifiers to lower case (SQL identifiers are normally supposed to be case insensitive)

So if you forced it to upper case on creation you need to force upper case to drop it too.

DROP SCHEMA "SCHEMA1" CASCADE;