PostgreSQL ALTER TABLESPACE

Summary: in this tutorial, you will learn how to use the PostgreSQL ALTER TABLESPACE statement to rename, change the owner, or set parameters for a tablespace.

Introduction to ALTER TABLESPACE statement

Once a tablespace is created, you can change its definition by using the ALTER TABLESPACE as shown below:

ALTER TABLESPACE tablespace_name
action;
Code language: SQL (Structured Query Language) (sql)

The action can be:

• Rename the tablespace
• Change the owner
• Sett the parameters for the tablespace.

To rename the tablespace, you use the ALTER TABLESPACE RENAME TO statement:

ALTER TABLESPACE tablespace_name 
RENAME TO new_name;
Code language: SQL (Structured Query Language) (sql)

To change the owner of the tablespace, you use the ALTER TABLESPACE OWNER TO statement:

ALTER TABLESPACE tablespace_name 
OWNER TO new_owner;
Code language: SQL (Structured Query Language) (sql)

The following statement changes the parameters for a tablespace:

ALTER TABLESPACE tablespace_name 
SET parameter_name = value;
Code language: SQL (Structured Query Language) (sql)

Only superusers or tablespace owners can execute the ALTER TABLESPACE statement.

PostgreSQL ALTER TABLESPACE examples

The following statement renames dvdrentaltablespace to dvdrental_raid:

ALTER TABLESPACE dvdrental 
RENAME TO dvdrental_raid;
Code language: SQL (Structured Query Language) (sql)

The following statement changes the owner of the  dvdrental_raid from postgresto hr:

ALTER TABLESPACE dvdrental_raid 
OWNER to hr;
Code language: SQL (Structured Query Language) (sql)

Summary

• Use ALTER TABLESPACE RENAME TO statement to rename a tablespace.
• Use ALTER TABLESPACE OWNER TO to change the owner of a tablespace.
• Use ALTER TABLESPACE SET to set the parameters for a tablespace.