PostgreSQL Tutorial: DROP TABLESPACE

Summary: In this tutorial, you will learn how to use the PostgreSQL DROP TABLESPACE statement to remove a tablespace.

Table of Contents

Introduction to PostgreSQL DROP TABLESPACE statement

The DROP TABLESPACE statement removes a tablespace from the current database:

DROP TABLESPACE [IF EXISTS] tablespace_name;

In this syntax, you specify the name of the tablespace that you want to remove after the DROP TABLESPACE keywords.

Yous use the IF EXISTS option to instruct PostgreSQL to issue a notice instead of an error when the tablespace does not exist.

Only tablespace owners or superusers can execute the DROP TABLESPACE statement to drop the tablespace.

PostgreSQL DROP TABLESPACE example

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

CREATE TABLESPACE demo 
LOCATION 'c:/data/demo';

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

CREATE DATABASE dbdemo 
TABLESPACE = demo;

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

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

The following statement returns all objects in the demo tablespace:

SELECT
	ts.spcname,
	cl.relname
FROM
	pg_class cl
JOIN pg_tablespace ts 
    ON cl.reltablespace = ts.oid
WHERE
	ts.spcname = 'demo';

Fourth, try to drop the demotablespace:

DROP TABLESPACE demo;

PostgreSQL issues an error:

[Err] ERROR: tablespace "demo" is not empty

Because the demo tablespace is not empty, it cannot be dropped.

Fifth, login to the postgresdatabase and drop the dbdemodatabase:

DROP DATABASE dbdemo;

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

ALTER DATABASE dbdemo
SET TABLESPACE = pg_default;

Sixth, delete the demo tablespace again:

DROP TABLESPACE demo;

It worked. The demo tablespace was dropped successfully.

In this tutorial, you have learned how to use the PostgreSQL DROP TABLESPACE statement to drop a tablespace.