PostgreSQL - RENAME DATABASE

Quiz

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 −

ID	Name	Age	Department	Salary	Join Date
1	Alex	28	IT	60000	2022-05-10
2	Robin	35	HR	55000	2020-08-22
3	Ravindra	30	Marketing	58000	2021-03-15
4	David	40	Finance	70000	2018-11-30

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 −

table_result_for_rename_database_in_postgresql

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;

sql_shell_psql_command_for_connecting_database

ii. Query to show the data.

SELECT * FROM Emp;

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

Print Page

Save or share this article