PostgreSQL Tutorial: 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.

Table of Contents

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;

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;

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

ALTER TABLESPACE tablespace_name 
OWNER TO new_owner;

The following statement changes the parameters for a tablespace:

ALTER TABLESPACE tablespace_name 
SET parameter_name = value;

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;

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

ALTER TABLESPACE dvdrental_raid 
OWNER to hr;

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.