Deleting Tablespaces Using PostgreSQL DROP TABLESPACE Statement

Summary: in this tutorial, you will learn how to remove a tablespace by using the PostgreSQL DROP TABLESPACE statement.

Introduction to DROP TABLESPACE statement

To remove a tablespace, you use DROP TABLESPACE statement as follows:

You need to specify the name of the tablespace after the DROP TABLESPACE clause. The IF EXISTS helps you avoid the error of removing a non-existent tablespace.

Only tablespace owner or the superuser can delete the tablespace.

Before deleting the tablespace, make sure that it is empty, which means there are no database objects inside it.

PostgreSQL DROP TABLESPACE example

First, create a new tablespace named demoand maps it to the c:\data\demo directory.

Second, create a new database named dbdemoand set its tablespace to demo:

Third, create a new table named testin the dbdemoand set it tablespaceto demo:

You can get all objects in the demotablespace by using the following query:

Fourth, try to delete the demotablespace:

We got an error message:

Because the demo tablespace is not empty, we could not delete it.

Fifth, login to the postgresdatabase and delete the dbdemodatabase:

Instead of deleting the database, you can move it to the another tablespace e.g.,  pg_default by using the ALTER TABLE statement as follows:

Sixth, delete the demotablespace again:

It worked. The demo tablespace was deleted.

In this tutorial, we have shown you how step by step how to delete tablespace by using the PostgreSQL DROP TABLESPACE statement.