DROP SCHEMA

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