CODEWITHSUNDEEP
postgresql
DanielBK
DanielBK

Reputation: 952

Cannot drop a schema in PostgreSQL

I am trying to drop the schema masterdata from a postgres database, but it does not work. I am using PostgreSQL 9.5. I have 2 databses, one of them has the masterdata schema, which I want to drop. Here is the structure (in DBeaver):

enter image description here

My first attempt was just to execute the SQL statement DROP SCHEMA masterdata in DBeaver, but it tells me, that such a schema does not exist (but it does show it, as we can see in the picture!). Maybe, it does not know, in which of the 2 databses to look for this schema? If so, then how to specify it? I was looking for a way to specify the database, but did not find anything.

However, my second attempt was to use psql and to type the command

psql -U postgres -d bobd -h localhost

So here I concretely specify, which databse to use! psql does not answer anything, it just asks for the next command. And when I type \dn to view the current schemas, then the schema masterdata is still there! Also, the data in the tables is still there. I tried the same with other users instead of the user postgres (also with the owner of the schema to remove), but the result is the same.

Any ideas, what I am doing wrong?

Upvotes: 1

Views: 9214

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 248030

Your DBeaver session was probably connected to the wrong database (postgres?).

Do it from the psql session, that is easiest.

Right after \dn showed you that there is indeed such a schema, enter

DROP SCHEMA masterdata;

It may complain that there are objects in that schema. Then you can use

DROP SCHEMA masterdata CASCADE;

Upvotes: 6

Bo.
Bo.

Reputation: 2541

Maybe your schema name contains characters that do not display or display differently (maybe capital letters) in DBeaver. I suggest you check names by running following query:

SELECT '~~' || nspname || '~~' FROM pg_catalog.pg_namespace;

Try adding quotes around schema name if you have added capital letters when creating schema.

Upvotes: 0

Related Questions