PostgreSQL DROP TABLESPACE Statement

Summary: In this tutorial, you’ll learn how to safely delete a tablespace using the PostgreSQL DROP TABLESPACE statement.

PostgreSQL DROP TABLESPACE Statement Overview #

To delete a tablespace, you use the DROP TABLESPACE statement. Here’s the basic syntax of the DROP TABLESPACE statement:

DROP TABLESPACE [IF EXISTS] tablespace_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the tablespace name you want to delete.
• Second, use the IF EXISTS option to delete a tablespace only if it exists conditionally.

Note that only superuser and tablespace owners or superusers can execute the DROP TABLESPACE statement. Additionally, a tablespace must be empty before you can drop it.

If a tablespace is not empty, you need to move the database objects inside it to another tablespace before deleting it.

We’ll create a new tablespace with a table and delete it.

Creating a new tablespace #

First, create a new directory for the new tablespace:

mkdir C:\pgdata\mytsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, connect to the PostgreSQL and create a new tablespace:

CREATE TABLESPACE myts 
LOCATION 'C:\pgdata\myts';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, create a new table and store its data in the myts tablespace:

CREATE TABLE t(id INT) TABLESPACE myts;
INSERT INTO t(id) VALUES (1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Dropping a tablespace #

First, attempt to drop the myts tablespace:

DROP TABLESPACE myts;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued an error:

ERROR: tablespace "myts" is not emptyCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The reason is that the myts stores the data of the table t.

Second, set the tablespace of the table t to the default tablespace:

ALTER TABLE t 
SET TABLESPACE pg_default;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, rerun the DROP TABLESPACE statement:

DROP TABLESPACE myts;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The statement deleted the myts tablespace successfully.

Summary #

• Use the PostgreSQL DROP TABLESPACE statement to drop a tablespace.
• Move all database objects out of a tablespace before dropping it.