PostgreSQL DROP TABLESPACE

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

Introduction to PostgreSQL DROP TABLESPACE statement

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

DROP TABLESPACE [IF EXISTS] tablespace_name;
Code language: SQL (Structured Query Language) (sql)

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';
Code language: SQL (Structured Query Language) (sql)

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

CREATE DATABASE dbdemo 
TABLESPACE = demo;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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';
Code language: SQL (Structured Query Language) (sql)

Fourth, try to drop the demotablespace:

DROP TABLESPACE demo;
Code language: SQL (Structured Query Language) (sql)

PostgreSQL issues an error:

[Err] ERROR: tablespace "demo" is not empty
Code language: PHP (php)

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

Fifth, login to the postgresdatabase and drop the dbdemodatabase:

DROP DATABASE dbdemo;
Code language: SQL (Structured Query Language) (sql)

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

ALTER DATABASE dbdemo
SET TABLESPACE = pg_default;
Code language: SQL (Structured Query Language) (sql)

Sixth, delete the demo tablespace again:

DROP TABLESPACE demo;
Code language: SQL (Structured Query Language) (sql)

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.