To store the data of the database, we need to offer some location to the data on the disk. This location is called the tablespace in PostgreSQL. The data can be tables, triggers indexes, etc. However, if a tablespace is no longer needed, it can be dropped in PostgreSQL. The tablespace can be dropped by the owner of the tablespace. In this blog, we will drop the tablespace in PostgreSQL.

How to Drop a Tablespace in PostgreSQL?

We can drop a tablespace in Postgres. The basic syntax for dropping the tablespace is given as:

DROP TABLESPACE [IF EXISTS] tabsp_name;

In this syntax:

● We write the command DROP TABLESPACE to drop the tablespace.

● The IF EXISTS clause is also written which will drop the tablespace only if it exists. If the tablespace does not exist, it will just raise a notice. In case this clause is not written it will simply throw an error if it encounters a case where a tablespace does not exist.

● These statements are followed by the name of the tablespace.

Let's see the dropping of the tablespace using an example.

Example: Dropping a Tablespace in Postgres

Consider the following queries to drop a tablespace. First of all, we will create a tablespace named “tablespace”.

CREATE TABLESPACE tablespace
LOCATION 'C:\Program Files\PostgreSQL\15\data';

This will successfully create a tablespace. The next step is to create a database for our tablespace like this:

CREATE DATABASE test_database 
TABLESPACE = tablespace;

The database will be created. After that, we will create a new table in the database with the following query:

CREATE   TABLE example (
  serial_no serial PRIMARY KEY,
  title VARCHAR (255) NOT NULL
   ) TABLESPACE tablespace;

The table will be created. To get objects of the “tablespace” tablespace, execute the following query:

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

This query will return all the objects in the tablespace. The output of the query looks like this:

Now we will try to drop the tablespace with the following query:

DROP TABLESPACE tablespace;

This will give an error that the tablespace is not empty.

The tablespace cannot be dropped until it is not empty. So we can simply drop the database.

DROP DATABASE test_database;

This statement will simply drop the database like this:

Or instead of deleting the database, we can also shift it to some other tablespace. Like in the below query, we have shifted the database to”pg_default” using the ALTER TABLESPACE statement. The query is:

ALTER DATABASE test_database
SET TABLESPACE = pg_default;

By following any of the above methods, we can now delete the tablespace as the tablespace becomes empty now so execute the following query again:

DROP TABLESPACE tablespace;

The query has dropped the tablespace successfully.

So this is how the tablespace is dropped.

Conclusion

To drop a tablespace in Postgres, execute the DROP TABLESPACE statement followed by the name of the tablespace to be discarded. In this blog, we got to know about dropping tablespace using practical examples. As we know the tablespace contains the database and tables. So we can not directly drop them if they are not empty. We will have to delete the database in them or may have to shift the database to some other tablespace. Then we can drop a tablespace.