How to rename PostgreSQL database

In this article, we will see How to rename PostgreSQL database. ALTER DATABASE command is used to change the name of database in PostgreSQL Server. Only the database owner or a superuser can rename a database and non-superuser owners must also have the CREATEDB privilege.

ALTER DATABASE Syntax:

ALTER DATABASE database_name RENAME TO new_database_name [ [ WITH ] option [ ... ] ]

To rename a PostgreSQL database, you use the following steps:

• Disconnect from the database that you want to rename and connect to a different database.
• Check and terminate all active connections to the database that you want to rename.
• Use the ALTER DATABASE statement to rename the database to the new one.

Rename PostgreSQL database example:

1. Lets verify the list of databases in PostgreSQL Server by using either meta-command or SQL query against system view pg_database.

\l

or

select datname from pg_database;

2. Now rename the database ‘qat1’ to ‘LONPROD1’ in PostgreSQL Server.

ALTER DATABASE qat1 RENAME to LONPROD1;

3. Lets verify database name changed or not.

select datname from pg_database;

Output:

postgres=# select datname from pg_database;
  datname
-----------
 postgres
 template1
 template0
 ecommerce
 r2schools
 newdb
 lonprod1
(7 rows)