Summary: in this tutorial, you will learn how to change the definition of a tablespace by using the PostgreSQL ALTER TABLESPACE statement.
Introduction to ALTER TABLESPACE statement
Once a tablespace is created, you can change its definition by using the ALTER TABLESPACE as shown below:
1 | ALTER TABLESPACE action; |
PostgreSQL provides some actions such as renaming tablespace name, changing the owner and setting tablespace’s parameters.
To change the name of the tablespace, use the following statement:
1 | ALTER TABLESPACE tablespace_name RENAME TO new_name; |
To change the owner of the tablespace, use the following statement:
1 | ALTER TABLESPACE tablespace_name OWNER TO new_owner; |
You can also change the tablespace’s parameters including seq_page_cost and random_page_cost, which specify the cost of reading pages from tables in the tablespace.
1 | ALTER TABLESPACE tablespace_name SET parameter = value; |
To execute the ALTER TABLESPACE statement, you must be a superuser or the owner of the tablespace.
Currently, PostgreSQL does not support change the location of the tablespace.
Notice that ALTER TABLESPACE statement is a PostgreSQL extension.
PostgreSQL ALTER TABLESPACE examples
The following statement renames dvdrentaltablespace to dvdrental_raid:
1 | ALTER TABLESPACE dvdrental RENAME TO dvdrental_raid; |
To change the owner of the dvdrental_raid from postgresto hr, use the following statement:
1 | ALTER TABLESPACE dvdrental_raid OWNER to hr; |
In this tutorial, you have learned how to change the tablespace’s definition by using the PostgreSQL ALTER TABLESPACE statement.