PostgreSQL | Schema | Drop a Schema (DROP SCHEMA)

Tags:

• PostgreSQL
• Database

This page explains how to delete a created schema by using the DROP SCHEMA command.

Drop a Schema

To delete a schema, use the DROP SCHEMA command. The syntax is as follows.

DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

This removes the specified schema (name). If the schema contains objects such as tables, specifying CASCADE deletes the schema together with the objects. Specifying RESTRICT cancels the deletion. The default is RESTRICT.

To delete a schema, the role executing the command must be a superuser or the schema owner. If CASCADE is specified, objects are deleted even if they have different owners.

–

Now try it in practice. Connect to the devkuma database in PostgreSQL as the regular user role kimkc. If you run the psql meta-command \dn, you can see that the current database has a fruit schema owned by the kimkc role.

devkuma=# \dn
 스키마(schema) 목록
   이름   |  소유주
----------+----------
 fruit    | kimkc
 myschema | postgres
 public   | postgres
(3개 행)


devkuma=#

Delete the fruit schema. Run the following.

devkuma=# drop schema fruit;
DROP SCHEMA
devkuma=#

The schema has been deleted. To confirm, run the \dn command again.

devkuma=# \dn
 스키마(schema) 목록
   이름   |  소유주
----------+----------
 myschema | postgres
 public   | postgres
(2개 행)


devkuma=#

You can confirm that the fruit schema has been deleted.

–

Now create an apple schema again by using the kimkc role.

C:\Users\kimkc>psql -U kimkc -d devkuma
kimkc 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.

devkuma=# create schema apple;
CREATE SCHEMA
devkuma=#

Create a table in the apple schema by using a superuser role different from the kimkc role.

C:\Users\kimkc>psql -U postgres -d devkuma
postgres 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.

devkuma=# create table apple.blog (id integer);
CREATE TABLE
devkuma=#

The apple schema now contains a table whose owner is different from the kimkc role.

devkuma=# \dt apple.*;
      릴레이션(relation) 목록
 스키마 | 이름 |  종류  |  소유주
--------+------+--------+----------
 apple  | blog | 테이블 | postgres
(1개 행)


devkuma=#

Now try deleting the apple schema by using the kimkc role.

C:\Users\kimkc>psql -U kimkc -d devkuma
kimkc 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.

devkuma=# drop schema apple;
오류:  기타 다른 개체들이 이 개체에 의존하고 있어, apple 스키마 삭제할 수 없음
상세정보:  apple.blog 테이블 의존대상: apple 스키마
힌트:  이 개체와 관계된 모든 개체들을 함께 삭제하려면 DROP ... CASCADE 명령을 사용하십시오
devkuma=#

The error message “오류: 기타 다른 개체들이 이 개체에 의존하고 있어, apple 스키마 삭제할 수 없음” is displayed, and the schema cannot be deleted.

If CASCADE is not specified explicitly, it is the same as specifying RESTRICT, so the schema cannot be deleted when objects exist in it.

This time, specify CASCADE and delete the apple schema. Run the following.

devkuma=# drop schema apple cascade;
알림:  apple.blog 테이블 개체가 덩달아 삭제됨
DROP SCHEMA
devkuma=#

The apple schema has been deleted. Although it contained a table created by a different owner, that table was deleted at the same time.

–

This page explained how to delete a created schema by using the DROP SCHEMA command.