In PostgreSQL, the tablespace offers the location on the disk where we can store all the data of the database. The data may include tables, triggers indexes, etc. The tablespace is usually used to map the logical name onto the physical address on the disk. Sometimes we need to alter the tablespace. The tablespace can be altered in many ways. In this post, we will learn about how we can alter the tablespace in PostgreSQL.

How to Alter a Tablespace in PostgreSQL?

We can alter our tablespace in many ways. We may need to alter the name of the tablespace i.e. rename the tablespace, change the owner of the tablespace, or maybe alter some parameter name. Let's try to alter these, one by one.

Rename the Tablespace

To change the name of the tablespace we will have to alter it. We use the ALTER TABLESPACE RENAME TO statement to rename the tablespace. The basic syntax looks like this:

ALTER TABLESPACE tabsp_name 
RENAME TO newName;

Example: Rename the Tablespace Using the ALTER statement

Let’s consider an example for this particular case:

ALTER TABLESPACE example_tablespace
RENAME TO tablespace;

We created a tablespace named example_tablespace in our previous article. Now we can rename the tablespace by using the above query. The output of the above query will be:

The name of the tablespace has been altered and the tablespace has been renamed to “tablespace” from “example_tablespace”.

Change/Switch the Owner of Tablespace

We can also change/switch the owner of any tablespace by using the ALTER TABLESPACE OWNER TO statement. The basic syntax for such a query is given as:

ALTER TABLESPACE tabsp_name 
OWNER TO newOwner;

Specify the name of the new owner after the OWNER TO clause. Let's do it using an example.

Example: Changing the Owner Name Using the ALTER Statement

Let’s change the name of the owner of the tablespace “tblsp_name” to “joseph”. The query will be:

ALTER TABLESPACE tblsp_name 
OWNER TO joseph;

By executing this command we will get:

Changing any Parameter Value

We can also change the value of any parameter using the ALTER statement. The basic query for this is:

ALTER TABLESPACE tabsp_name 
SET para_name = val;

These were the use cases of the ALTER statement. We can rename a tablespace, we can switch/change the owner of the tablespace and we can replace/change the value of any parameter.

Conclusion

To alter a tablespace in PostgreSQL, the “ALTER TABLESPACE” statement is used with “RENAME TO”, “OWNER TO”, and “SET” clauses. By doing so, we can rename a tablespace, we can switch/change the owner of the tablespace, and can replace/change the value of any parameter. In this article, we have learned to do all this by making use of the ALTER statement.