2

The answer to this question suggests I can use the CASCADE option of DROP SCHEMA to remove all a schema and all its tables, but warns:

CASCADE - Automatically drop objects (tables, functions, etc.) that are contained in the schema, and in turn all objects that depend on those objects

Being a novice to databases, I'm unsure about what objects may depend on objects within a schema. Is it possible that a table or some other object outside the schema that is dependent on an object inside the schema will be dropped as well? If I just want to eliminate a schema and all its contents without changing anything else, is using CASCADE a good idea?

4

run it in a transaction, you'll get warnings for every cascading drop, ROLLBACK if don't like what you see.

0

I can't think of a case where an ordinary table in a different schema would get dropped.

If there is a foreign key constraint between tables in different schemas, the constraint itself would get dropped but the table in the non-dropped schema would not get dropped.

If the dropped schema contained a type which was used for a column in a table in a different schema, then that column of that table would get dropped, but the table itself would not.

I think the only real answer to this type of question is "try it in a test instance and see if you like the result". In this case, you should be able to set up a test instance with just the objects, not the data itself (e.g. using pg_dump -s on production and replaying it into the test server) and very quickly test it out and see what objects get dropped.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.