PostgreSQL – Drop Schema

You can drop a schema and all of its objects using the DROP SCHEMA statement. This statement should be executed with caution because it permanently deletes the schema and all the objects within it.

Here’s the basic syntax to drop a schema:

DROP SCHEMA [IF EXISTS] schema_name [CASCADE | RESTRICT];

where

• schema_name is the name of the schema you want to drop.
• IF EXISTS (optional): This clause ensures that an error is not thrown if the schema doesn’t exist. If the schema exists, it will be dropped. If it doesn’t exist, nothing happens.
• CASCADE (optional): This option will automatically drop all objects within the schema before dropping the schema itself. Be very careful when using CASCADE because it can lead to data loss. Make sure you really want to delete everything in the schema.
• RESTRICT (optional): This option prevents dropping the schema if it contains any objects. If there are objects within the schema, you will receive an error, and the schema won’t be dropped.

Here are some examples:

1. Drop a schema without objects:

DROP SCHEMA my_schema;

This statement will drop the my_schema schema if it exists and doesn’t contain any objects.

2. Drop a schema with all objects within it:

DROP SCHEMA my_schema CASCADE;

This statement will drop the my_schema schema and all objects within it.

3. Drop a schema only if it’s empty:

DROP SCHEMA my_schema RESTRICT;

This statement will drop the my_schema schema only if it’s empty. If there are objects within it, you will receive an error.

Make sure to exercise caution when using the DROP SCHEMA statement, especially with the CASCADE option, as it can result in data loss if not used carefully.