Cannot drop a schema in PostgreSQL

Question

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):

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?

Laurenz Albe · Accepted Answer · 2020-09-07 08:22:35Z

7

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;

answered Sep 7, 2020 at 8:22

Laurenz Albe

256k22 gold badges310 silver badges386 bronze badges

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

4 Comments

DanielBK Over a year ago

Oh, I forgot to add in the question, that of course i did run DROP SCHEMA masterdata and it had no effect. My mistake :( But now I tried it again and ... it worked! :) Maybe it was because I added "CASCADE" ... though, as far I remember, I was trying this too. So in the end, I have no idea what exactly did the trick. I also did as Bo said in his answer to look at the namespaces. Indeed, there was no namespace "masterdata". But tehere was some strange namespace like "pg_toast" so I removed that. Maybe that had some effect too - i don't know. I any case thanks a lot!

Laurenz Albe Over a year ago

If you actually removed pg_toast (which I doubt you can), your database would be - well - toast. This is where overlength data are stored, so without it some of your data would be missing.

Software Engineer Over a year ago

Yeah YOU NEED TO TYPE IT TWICE IN ORDER TO TAKE EFFECT WHICH IT IS WEIRD.

Laurenz Albe Over a year ago

@SoftwareEngineer No, you need not.

Bo. · Accepted Answer · 2020-09-07 08:05:49Z

0

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.

answered Sep 7, 2020 at 8:05

Bo.

2,5253 gold badges24 silver badges36 bronze badges

Collectives™ on Stack Overflow

Cannot drop a schema in PostgreSQL

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related