Cloud

DROP SCHEMA

The DROP SCHEMA statement removes a SQL schema from the Redpanda SQL database. With CASCADE, it also removes the objects in the schema, such as views, catalog connections, and storage definitions.

Syntax

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

  • IF EXISTS: Optional. If the schema does not exist, the statement returns no error.

  • schema_name: Name of the schema to remove.

  • CASCADE: Optional. Also removes every object in the schema, including views, catalog connections, and storage definitions. Removing a catalog connection also removes the tables that you mapped through it.

  • RESTRICT: Optional. Removes the schema only if it is empty. This is the default behavior.

Behavior

  • Without CASCADE, you cannot drop a schema that contains objects. The statement fails with cannot drop schema: <schema_name> because other objects depend on it.

  • You cannot drop the public schema. The statement fails with permission denied: deletion of default schema public is not allowed.

  • You cannot drop the built-in schemas pg_catalog, information_schema, and system.

The DROP SCHEMA …​ CASCADE statement removes the catalog connections and storage definitions in the schema. It also removes the tables that you mapped through the catalog connections. Check what the schema holds before you use CASCADE.

The DROP SCHEMA …​ CASCADE statement does not remove views in other schemas that read objects in the dropped schema. Those views remain, but queries of them fail. Before you drop a schema, find the views in other schemas that depend on it. Then drop those views. To list views and their definitions, query the pg_views system catalog.

Permissions

Only the owner of the schema or a superuser can drop it. In Redpanda Cloud, a user with the SQL: Manage permission is a superuser. See Manage access to Redpanda SQL.

Examples

Remove the empty schema analytics:

DROP SCHEMA analytics;

Remove the schema only if it exists:

DROP SCHEMA IF EXISTS analytics;

Remove the schema and every object in it. Before you use CASCADE, read the warning in Behavior:

DROP SCHEMA analytics CASCADE;

Suggested reading