PostgreSQL DROP TABLESPACE Statement

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 delete a tablespace from a database:

Here’s the syntax of the DROP TABLE statement:

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

In this syntax:

• First, specify the name of the tablespace that you want to remove after the DROP TABLESPACE keywords.
• Second, 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 examples

Let’s explore some examples of using the DROP TABLESPACE statement.

1) Basic DROP TABLESPACE statement example

First, open the Command Prompt or Terminal on a Unix-like system and create a new directory for the tablespace such as C:\pgdata\demo:

mkdir C:\pgdata\sample

Next, connect to the PostgreSQL server:

psql -U postgres

Then, create a new tablespace called sample_ts:

CREATE TABLESPACE sample_ts
LOCATION 'C:/pgdata/demo';Code language: JavaScript (javascript)

After that, drop the sample_ts tablespace using the DROP TABLESPACE statement:

DROP TABLESPACE sample_ts;

Finally, exit psql:

exitCode language: PHP (php)

2) Dropping a tablespace that has objects

First, create a new directory called C:/pgdata/demo on your server. Replace the path with the actual one that you use:

mkdir C:\pgdata\demo

Second, create a new tablespace named demo and map it to the c:\pgdata\demo directory.

CREATE TABLESPACE demo_ts 
LOCATION 'C:/pgdata/demo';Code language: SQL (Structured Query Language) (sql)

Third, create a new database named dbdemo and set its tablespace to demo:

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

Fourth, connect to the demodb database:

\c demodb

Fifth, create a new table named testin the dbdemo and set it tablespace to demo_ts:

CREATE TABLE test (
  id serial PRIMARY KEY, 
  title VARCHAR (255) NOT NULL
) TABLESPACE demo;Code language: SQL (Structured Query Language) (sql)

Sixth, attempt to drop the demo tablespace:

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

PostgreSQL issues an error:

[Err] ERROR: tablespace "demo_ts" is not emptyCode language: PHP (php)

Because the demo_ts tablespace is not empty, you cannot drop it.

Seventh, connect to the postgres database:

\c postgres

Eight, drop the demodb database:

DROP DATABASE demodb;

Ninth, drop the demo_ts tablespace:

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

Instead of dropping the database, you can move it to another tablespace such as pg_default by using the ALTER TABLE statement as follows:

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

And then delete the demo_ts tablespace again:

DROP TABLESPACE demo_tsCode language: SQL (Structured Query Language) (sql)

Summary

• Use the PostgreSQL DROP TABLESPACE statement to drop a tablespace.