Summary: in this tutorial, you will learn how to modify existing databases by using PostgreSQL ALTER DATABASE statement.
Introduction to PostgreSQL ALTER DATABASE statement
Once you created a database, you can change its features by using the ALTER DATABASE statement as shown following:
1 | ALTER DATABASE target_database action; |
You specify the database name that you want to change after the ALTER DATABASE. PostgreSQL allows you to perform various actions on the existing database. Let’s examine each action in more detail.
Rename database
To rename the database, you use ALTER DATABASE RENAME TO statement as follows:
1 | ALTER DATABASE target_database RENAME TO new_database; |
To rename a database, you have to connect to another database e.g., postgres.
Change owner
To change the owner of the database, you use ALTER DATABASE OWNER TO as the following statement:
1 | ALTER DATABASE target_database OWNER TO new_onwer; |
Only the superuseror owner of the database can change the database’s owner. The database owner must also have the CREATEDB privilege to rename the database.
Change tablespace
To change the default tablespace of the database, you use ALTER DATABASE SET TABLESPACE as follows:
1 | ALTER DATABASE target_database SET TABLESPACE new_tablespace; |
The statement moves tables and indexes from the legacy tablespace to the new one.
Change session defaults for run-time configuration variables
Whenever you connect to a database, PostgreSQL loads the configuration variables presented in the postgresql.conf file and uses these variables by default.
To override these settings for a particular database, you use ALTER DATABASE SET statement as follows:
1 | ALTER DATABASE target_database SET configuration_parameter = value; |
In the subsequent sessions, PostgreSQL will override the settings in the postgresql.conf file.
Notice that only a supperuser or the database owner can change the default session variables for a database.
PostgreSQL ALTER DATABASE example
First, let’s log in as the postgres user and create a new database named testdb2 for the demonstration.
1 | CREATE DATABASE testdb2; |
Second, use the following statement to rename the testdb2 database to testhrdb;
1 | ALTER DATABASE testdb2 RENAME TO testhrdb; |
Third, execute the following statement to change the owner of the testhrdb database from postgres to hr, with the assumption that the hr role already exists.
1 | ALTER DATABASE testhrdb OWNER TO hr; |
If the hr role does not exist, create it by using the following statement:
1 2 | CREATE ROLE hr VALID UNTIL 'infinity'; |
Fourth, change the default tablespace of the testhrdb from pg_default to hr_default, with the assumption that the hr_defaulttablespace already exists.
1 2 | ALTER DATABASE testhrdb SET TABLESPACE hr_default; |
If the hr_defaulttablespace does not exist, you can create it by using the following statement:
1 2 3 | CREATE TABLESPACE hr_default OWNER hr LOCATION E'C:\\pgdata\\hr'; |
Fifth, to set escape_string_warning configuration variable to off, you can use the following statement:
1 | ALTER DATABASE testhrdb SET escape_string_warning TO off; |
In this tutorial, we have shown you how to change the existing database’s features and configuration parameters by using the PostgreSQL ALTER DATABASE statement.