Alter Database in PostgreSQL

In this PostgreSQL tutorial, you will learn everything about alter database in PostgreSQL with the help of examples. In the previous, tutorial we have seen all about drop databases.PostgreSQL provides an ALTER DATABASE statement to change or alter the existing PostgreSQL database.

Interoduction of PostgreSQL ALTER DATABASE Statement

To change or alter the database, PostgreSQL provides us a ALTER DATABASE statement that is capable of changing all the information of details of the existing database.

The first form changes certain per-database settings, The only superuser and database have permission to do these changes.

Syntax

This is the basic syntax of the PostgreSQL ALTER DATABASE statement.

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

ALTER DATABASE name RENAME TO new_name

ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

Parameters

• name:- Name of the database whose attribute is to be altered.
• allowcon:- If it is true then no one can able to connect with this database.
• connlimit:- This specifies, how many concurrent connections can be made to this database.-1 means no limit.
• istemplate:- if true, then this database can be cloned by any user with createdb privileges.
• new_name:- New name of the database.
• new_owner:- This specifies, the new owner of the database.
• new_tablespace:- The new default tablespace of the database.
• configuration_parameter and value:- Set this database session default for the specified configuration parameter to the given value.

1.) Changing the attributes of database.

To change the attribute of the database, you have to use the following statement.

ALTER DATABASE name WITH option;

Where option can be following.

IS_TEMPLATE
CONNECTION LIMIT
ALLOW_CONNECTION

2.) Rename the database name

You have to use the following query to change the database name.

ALTER DATABASE database_name RENAME TO new_name;

Note:- It is not possible to rename the currently open database, you have to switch to another database and rename.

3). Change the owner of the database

The following ALTER DATABASE statement is used to change the owner of the database.

ALTER DATABASE database OWNER TO new_owner | surrent_user | session_user;

4). Changing the default tablespace of database.

The following statement changes the default tablespace of the database.

ALTER DATABASE database_name SET TABLESPACE new_tablespace;

PostgreSQL ALTER DATABASE Example:

Firstly, we will log in to the Postgres server using Postgres user and create a new database name demo1 for demonstration.

CREATE DATABASE demo1;

Rename demo1 to demo2 using the following query.

ALTER DATABASE demo1 RENAME TO demo2;

After successfully executing the above command, you will see ALTER DATABASE message.

Now, we will change the owner of the database Postgres to codingapp, using the following query.

ALTER DATABASE demo2 OWNER TO codingapp;

If the role does not exist, you can create it by using the following query.

CREATE ROLE coddingapp LOGIN CREATEDB PASSWORD 'write your password here';

Now, we will change the default tablespace of the demo2 database from pg_default to demo2_space, using the following query.

ALTER DATABASE demo2 SET TABLESPACE demo2_space.

If the tablespace does not exist, you can create it by using the following query.

CREATE TABLESPACE demo2_space OWNER coddingapp LOCATION 'c:\\sampledb\\hr;

Conclusion

In this article, we have seen all about alter database in PostgreSQL with the help of various examples. ALTER DATABASE is one of the best Postgres commands to alter or change the Postgres database.
I hope you have understood this article well. If you like this article, please share and keep visiting further for interesting PostgreSQL tutorials.

Reference:- Click Here

PostgreSQL Tutorials:- Click Here

FAQs – Frequently Asked Questions

---

Thanks for your valuable time 👏👏👏 …..