DROP SCHEMA
Permanently removes a schema from the database. Be sure that you want to remove the schema before you drop it, because DROP SCHEMA is an irreversible process. Use the CASCADE parameter to drop a schema containing one or more objects.
Syntax
DROP SCHEMA [ IF EXISTS ] [database.]schema[,…] [ CASCADE | RESTRICT ]
Parameters
IF EXISTS
|
Specifies not to report an error if the schemas to drop do not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them. |
| [database.]schema | Name of the schema to drop. If you specify a database, it must be the current database. |
CASCADE
|
Specifies to drop the schema and all objects in it, regardless of who owns those objects. Objects in other schemas that depend on objects in the dropped schema—for example, user-defined functions—also are silently dropped. |
RESTRICT
|
Drops the schema only if it is empty (default). |
Privileges
Non-superuser: schema owner
Restrictions
- You cannot drop the PUBLIC schema.
- If a user is accessing an object within a schema that is in the process of being dropped, the schema is not deleted until the transaction completes.
- Canceling a DROP SCHEMA statement can cause unpredictable results.
Examples
The following example drops schema S1 only if it doesn't contain any objects:
=> DROP SCHEMA S1;
The following example drops schema S1 whether or not it contains objects:
=> DROP SCHEMA S1 CASCADE;