PostgreSQL ALTER DATABASE

 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:

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:

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:

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:

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:

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.

Second, use the following statement to rename the testdb2 database 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.

If the hr role does not exist, create it by using the following statement:

Fourth, change the default tablespace of the testhrdb from pg_default to hr_default, with the assumption that the hr_defaulttablespace already exists.

If the hr_defaulttablespace does not exist, you can create it by using the following statement:

Fifth, to set escape_string_warning configuration variable to off, you can use the following statement:

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.