In PostgreSQL, the DROP TABLESPACE statement is used to remove a tablespace.
Syntax: DROP TABLESPACE [IF EXISTS] tablespace_name;
Let’s analyze the above syntax:
First, specify the name of the tablespace that is to be deleted after the DROP TABLESPACE keywords.
Second, use the IF EXISTS option to handle errors in case the tablespace doesn’t exists.
Note: It is important to note that only tablespace owners or superusers can execute the DROP TABLESPACE statement.
Example:
First, we create a new tablespace named gfg and map it to the C:\data\gfg directory:
CREATE TABLESPACE gfg
LOCATION 'C:\data\gfg';
Now create a database named db_gfg and set its tablespace to gfg:
CREATE DATABASE db_gfg
TABLESPACE = gfg;
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;
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:
Now if you try to drop the gfgtablespace:
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;
Now check for the tablespace using the below command to verify:
\db+
Output:
Comments