Updated on 2025-05-29 GMT+08:00

DROP SCHEMA

Description

Drops a schema from the current database.

Precautions

  • Only the owner of a schema or a user granted the DROP permission for a schema has the permission to run the DROP SCHEMA command. If separation of duties is disabled, the system administrator has this permission by default.
  • Users except the initial user and O&M administrator cannot drop the schema of the O&M administrator.
  • You are not allowed to delete DBE_PLDEVELOPER when allow_system_table_mods is disabled.
  • Only the initial user has the permission to delete system schemas.

Syntax

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

Parameters

  • IF EXISTS

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

  • schema_name

    Specifies the schema name.

    Value range: an existing schema name

  • CASCADE | RESTRICT
    • CASCADE: automatically deletes all the objects contained in the schema.
    • RESTRICT: refuses to delete the schema if the schema contains objects. This is the default action.

Schemas beginning with pg_temp or pg_toast_temp are for internal use. Do not delete them. Otherwise, unexpected consequences may be incurred.

The schema currently being used cannot be deleted. To delete it, switch to another schema first.

Examples

-- Create role role2.
gaussdb=# CREATE ROLE role2 IDENTIFIED BY '********';

-- Create a schema based on the name.
gaussdb=# CREATE SCHEMA schema1 AUTHORIZATION role2;
CREATE SCHEMA

-- Drop the schema.
gaussdb=# DROP SCHEMA schema1 CASCADE;

-- Drop the user.
gaussdb=# DROP USER role2 CASCADE;

Helpful Links

ALTER SCHEMA and CREATE SCHEMA