DROP SCHEMA

This page documents the preview version (v2.21). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

Synopsis

Use the DROP SCHEMA statement to remove a schema and all of its associated objects from the system. This is an irreversible statement.

Syntax

drop_schema ::= DROP SCHEMA [ IF EXISTS ] schema_name [ , ... ] 
                [ CASCADE | RESTRICT ]

drop_schema

DROPSCHEMAIFEXISTS,schema_nameCASCADERESTRICT

Semantics

  • DROP SCHEMA... CASCADE executes in a single transaction so that either it has no effect (if it's interrupted) or the nominated schema together with all the objects in it are dropped.

drop_schema

DROP SCHEMA [ IF EXISTS ] schema_name

Remove a schema from the database. The schema can only be dropped by its owner or a superuser.

schema_name

Specify the name of the schema.

CASCADE

Remove a schema and all associated objects. All objects that are associated with schema_name such as tables will be invalidated after the drop statement is completed.

RESTRICT

Refuse to drop the schema if it contains any objects. This is the default.

Example

Create a schema with a table:

CREATE SCHEMA sch1;
CREATE TABLE sch1.t1(id BIGSERIAL PRIMARY KEY);

Try to drop the schema:

DROP SCHEMA sch1;
ERROR:  cannot drop schema sch1 because other objects depend on it
DETAIL:  table sch1.t1 depends on schema sch1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Drop a schema with CASCADE:

DROP SCHEMA sch1 CASCADE;
NOTICE:  drop cascades to table sch1.t1
DROP SCHEMA

See also