PostgreSQL - DROP TABLESPACE

In PostgreSQL, the DROP TABLESPACE statement is used to remove a tablespace. A tablespace is a storage location where PostgreSQL stores database objects such as tables and indexes. This command is useful for managing and cleaning up database storage.

Let us get a better understanding of the DROP TABLESPACE Statement in PostgreSQL from this article.

Syntax

DROP TABLESPACE [IF EXISTS] tablespace_name;

Parameters

Let's analyze the above syntax:

• tablespace_name: Specify the name of the tablespace you want to delete.
• IF EXISTS: This optional clause helps avoid errors if the tablespace does not exist. It prevents the command from failing if the tablespace is missing.

Note: It is important to note that only tablespace owners or superusers can execute the DROP TABLESPACE statement.

PostgreSQL DROP TABLESPACE Example

Let us look at an example of DROP TABLESPACE Statement in PostgreSQL to better understand the concept.

Step 1: Create a Tablespace

First, we create a new tablespace named 'gfg' and map it to the 'C:\data\gfg' directory:

CREATE TABLESPACE gfg
LOCATION 'C:\data\gfg';

Step 2: Create a Database with the Tablespace

Now create a database named 'db_gfg' and set its tablespace to 'gfg': 

CREATE DATABASE db_gfg 
TABLESPACE = gfg;

Step 3: Create a Table with the Tablespace

Now create a new table named 'test' in the 'db_gfg' and set it tablespace to 'gfg':

CREATE TABLE test (
    ID serial PRIMARY KEY,
    title VARCHAR (255) NOT NULL
) TABLESPACE gfg;

Step 4: Verify Objects in the Tablespace

The following statement returns all objects in the gfg tablespace:

SELECT
    table_space.spcname,
    class.relname
FROM
    pg_class class
JOIN pg_tablespace table_space 
    ON class.reltablespace = table_space.oid
WHERE
    table_space.spcname = 'gfg';

This will lead to the following:

Step 5: Drop the Tablespace

Now if you try to drop the 'gfg' tablespace:

DROP TABLESPACE gfg;

This will raise the following error:

As the gfg tablespace is not empty, it is not possible to drop the tablespace. Now, login to the Postgres database and drop the 'db_gfg' database:

DROP DATABASE db_gfg;

Now delete the gfg tablespace again:

DROP TABLESPACE gfg;

Step 7: Verify Deletion

Now check for the tablespace using the below command to verify:

\db+

Output:

Important Points About PostgreSQL DROP TABLESPACE Statement

• Ensure you have the necessary permissions to drop the tablespace. Only the owner or a superuser can perform this action.
• A tablespace must be empty of all objects before it can be deleted. This includes databases, tables, indexes, and any other database objects.
• Always use the IF EXISTS clause in scripts to prevent errors during execution, especially in automated processes.