PostgreSQL - RENAME DATABASE

---

---

The RENAME DATABASE is used to change the name of the database. After renaming, if you try to access the database using its old name, PostgreSQL will show an error like "database_name doesn't exist" because the old name is no longer valid. So, you must use the new name to access the database.

Syntax

Following is the syntax of RENAME DATABASE in PostgreSQL −

ALTER DATABASE old_database_name RENAME TO new_database_name;

Below is the key parameters of above syntax −

• old_database_name : The database which is already exists.
• new_database_name : The new name of the database.

Why to rename the database?

There are following reason to rename the name of the existing database −

• Name Standardization : To follow the consistent naming convention.
• Correction : The database name was initially not correct.
• Rebranding : This is required for the organization when they change the name of the project.
• Merging Databases : This differentiate the old vs new versions.
• Changing Databases : When transferring the database to a new owner or any team.

How RENAME database works?

To understand renaming a database in PostgreSQL, imagine you have an existing database with tables, rows, and columns. When you rename the database, all the data and structure remain same; only the database name changes. The data is simply becomes accessible under the new name. Let us see the demonstration −

Step 1 : Suppose this is an existing database or a new one that will be implemented on the PostgreSQL platform.

CREATE DATABASE employee_db_new;

Step 2 : To generate the following table in PostegreSQL, use the following lines of query −

CREATE TABLE Emp (
    ID SERIAL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT NOT NULL,
    Department VARCHAR(50) NOT NULL,
    Salary INT NOT NULL,
    Join_Date DATE NOT NULL
);

The above query produces the following result −

CREATE TABLE
Query returned successfully in 62 msec.

Step 3 : Insert data into the table.

INSERT INTO employees (Name, Age, Department, Salary, Join_Date)
VALUES
    ('Alex', 28, 'IT', 60000, '2022-05-10'),
    ('Robin', 35, 'HR', 55000, '2020-08-22'),
    ('Ravindra', 30, 'Marketing', 58000, '2021-03-15'),
    ('David', 40, 'Finance', 70000, '2018-11-30');

The above query print the following result −

INSERT 0 
Query returned successfully in 57 msec.

Step 4 : Get the table result using select statement.

SELECT * FROM Emp;

In PostgreSQL, select statement produce the following result −

Step 5 : Rename the database employee_db_new to emp_db. Follow the stepwise query which is mentioned below −

• Disconnect the active connection which means no active connection are using the database.

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'employee_db_new';

In this output, you will see the query to terminate the database connection.

• Rename the database from employee_db_new to emp_db and make sure all the data present in the new database.

ALTER DATABASE employee_db_new RENAME TO emp_db;

The result of above query is as follows −

ERROR:  database "employee_db_new" does not exist 

• Verify the data in the new database.

Open the window terminal follow the steps:

SQL Shell (psql) −> \l (check for list of existing database) −> \c emp_db;

i. Use the database_name to connect with server.

\c emp_db; 

ii. Query to show the data.

SELECT * FROM Emp; 

Thus, this way you can rename the database in PostgreSQL.