Help Center/ Data Warehouse Service / SQL Syntax Reference (8.1.3.x)/ DDL Syntax/ DROP SCHEMA
Updated on 2025-09-18 GMT+08:00
View PDF

DROP SCHEMA

Function

DROP SCHEMA deletes a schema in a database.

Precautions

  • Only the owner of a schema or a user granted with the DROP permission for the schema or a system administrator has the permission to execute the Drop SCHEMA statement.
  • Be cautious when using DROP OBJECT (e.g., DATABASE, USER/ROLE, SCHEMA, TABLE, VIEW) as it may cause data loss, especially with CASCADE deletions. Always back up data before proceeding.
  • For more information about development and design proposal, see Development and Design Proposal.

Syntax

1
 
DROP SCHEMA [ IF EXISTS ] schema_name [, ...] [ CASCADE | RESTRICT ];

Parameter Description

Table 1 DROP SCHEMA parameters

Parameter

Description

Value Range

IF EXISTS

Sends a notice instead of an error if the specified schema does not exist.

-

schema_name

Specifies the name of the schema to be deleted.

An existing schema name.

CASCADE | RESTRICT

Specifies how to process related data in the dependent object when a delete operation is performed.
  • CASCADE: automatically deletes all objects that are contained in the schema to be deleted.
  • RESTRICT: refuses to delete the schema that contains any objects. This is the default.
  • Do not delete the schemas with the beginning of pg_temp or pg_toast_temp. They are internal system schemas, and deleting them may cause unexpected errors.
  • A user cannot delete the schema in use. To delete the schema in use, switch to another schema.

Example

Delete ds_new schema.

DROP SCHEMA ds_new;

Links

ALTER SCHEMA, CREATE SCHEMA

Parent Topic: DDL Syntax