# PostgreSQL ALTER DATABASE Statement

> Postgres’ ALTER DATABASE command modifies the already existing databases, such as renaming a database, modifying the database attributes, changing the database…

Postgres supports an **ALTER DATABASE** statement that assists us in modifying the already existing databases. For instance, the ALTER DATABASE command allows Postgres users to rename a database, modify the database attributes, change the database ownership, reset configuration parameters, etc.

This Postgres guide presents a detailed overview of the **ALTER DATABASE** command using suitable examples.

 **How to Rename a Database in Postgres?**

Utilize the below-provided syntax to rename an already existing database via the **ALTER DATABASE** command:
    
    
    ALTER DATABASE db_name RENAME TO modified_name;

To rename a database, you must be a superuser or database owner with CREATEDB privileges. Moreover, the current database can’t be renamed in PostgreSQL. To accomplish this task, you need to establish a connection with some other database.

 **Example: Renaming a Database Via ALTER DATABASE Command**

Firstly, execute the “\l” command to list the available databases:
    
    
    \l

Suppose we want to rename a database named “postgres_copy” to “postgres_db”. For this purpose, the ALTER DATABASE command will be executed as follows:
    
    
    ALTER DATABASE postgres_copy
    RENAME TO postgres_db;

Let’s verify the database modification using the “\l” command:
    
    
    \l

The output clarifies that the selected database has been renamed to “postgres_db”.

 **How to Alter Database Attributes in Postgres?**

Utilize the below syntax to modify the attributes of an already existing database via the **ALTER DATABASE** command:
    
    
    ALTER DATABASE db_name WITH option;

Where the “option” parameter can be replaced with one of the following:

 **\- IS_TEMPLATE** : the value of the stated parameter can be either true or false. “true” indicates that the selected database can be cloned/copied by any user having CREATEDB rights. While specifying “false” means only superusers or the database owner can clone the selected database.  
**-** **ALLOW_CONNECTIONS** : If the "false" value is specified, establishing a connection with the selected database will not be possible.  
 **-** **CONNECTION LIMIT** : It determines how many concurrent connections can be established with a particular database. Specifying -1 indicates no connection limit.

To modify database attributes, you must be a superuser or database owner.

 **Example 1: Modifying Database Attributes**

The following example demonstrates how to modify a database attribute in Postgres:
    
    
    ALTER DATABASE postgres_db
    ALLOW_CONNECTIONS = FALSE;

The above query specifies a “ **false** ” value for the “ **ALLOW_CONNECTIONS** ” parameter so that no one can establish the connection with the “postgres_db” database:

Let’s execute the “\c” command followed by the respective database name to verify the working of the “ALLOW_CONNECTIONS” parameter:
    
    
    \c postgres_db;

The output clearly states that you can’t establish a connection with the “postgres_db”.

 **Note:** Similarly, the IS_TEMPLATE and CONNECTION LIMIT parameters can be used to change the database attributes.

 **How to Alter Database Owners in Postgres?**

Use the ALTER DATABASE command with the OWNER TO clause to alter the database owner:
    
    
    ALTER DATABASE db_name
    OWNER TO new_db_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER;

The superuser and database owner with CREATEDB privileges can alter the database owner.

 **Example: Changing the Database Owner in Postgres**

As shown in the following snippet, "postgres" owns the "postgres_db" database:

To change the owner of “postgres_db”, execute the below-provided command:
    
    
    ALTER DATABASE postgres_db
    OWNER TO sample_user;

Execute the “\l” command to verify the owner of the “postgres_db”:
    
    
    \l

The owner of the selected database has been changed successfully.

 **How to Alter Default Tablespace in Postgres?**

The “tablespace” represents a directory/location where Postgres saves the data files. To alter a tablespace of a particular database, the “ **ALTER DATABASE** ” command is used with the “ **SET TABLESPACE** ” clause:
    
    
    ALTER DATABASE db_name
    SET TABLESPACE new_tablespace;

To alter the database tablespace, you must be a superuser or database owner.

 **Example: Changing Databse’s Tablespace**

We have already created a tablespace named “sample_tablespace”. In the following example, we will execute the ALTER DATABASE command to change the default tablespace of the “postgres_db” database:
    
    
    ALTER DATABASE postgres_db
    SET TABLESPACE  sample_tablespace;

The tablespace has been successfully changed.

 **How to Alter Defaults Runtime Configuration Parameters in Postgres?**

By default, PostgreSQL loads the configuration parameters from the "postgresql.conf" file when it establishes a connection with a database. However, the ALTER DATABASE command assists us in overriding or altering these settings for a specific database:
    
    
    ALTER DATABASE db_name
    SET configuration_parameter = parameter_value;

Only the superusers and database owners can alter the database’s default run-time configuration parameters.

 **Example: Changing Defaults Runtime Configuration Parameters/Variables**

The following example addresses the “escape_string_warning” parameter for a database named “postgres_db”:
    
    
    ALTER DATABASE postgres_db
    SET escape_string_warning = off;

The database named “ **postgres_db** ” has been successfully altered.

 **Conclusion**

Postgres’ **ALTER DATABASE** command modifies the already existing databases, such as renaming a database, modifying the database attributes, changing the database ownership, setting the configuration parameters, etc. Only superusers or database owners can modify the existing databases. This post presented an in-depth understanding of the ALTER DATABASE statement using numerous examples.

---
[View this page online](https://www.commandprompt.com/education/postgresql-alter-database-statement/)