PostgresqlHow-ToBeginner · 3 min read

How to Rename Database in PostgreSQL Quickly and Safely

To rename a database in PostgreSQL, use the ALTER DATABASE old_name RENAME TO new_name; command. You must connect to a different database (not the one being renamed) to run this command successfully.

📐

Syntax

The basic syntax to rename a database in PostgreSQL is:

ALTER DATABASE: The command to change database properties.
old_name: The current name of the database you want to rename.
RENAME TO: Keyword to specify the new name.
new_name: The new name you want to assign to the database.

Note: You cannot rename the database you are currently connected to.

sql

ALTER DATABASE old_name RENAME TO new_name;

💻

Example

This example shows how to rename a database named mydb to mydb_renamed. First, connect to the postgres database or any other database except mydb. Then run the rename command.

sql

\c postgres
ALTER DATABASE mydb RENAME TO mydb_renamed;

Output

You are now connected to database "postgres". ALTER DATABASE

⚠️

Common Pitfalls

Common mistakes when renaming a database in PostgreSQL include:

Trying to rename the database you are currently connected to, which causes an error.
Not having sufficient privileges; you need to be the owner or a superuser.
Using the wrong database name or misspelling it.

Always connect to a different database before renaming.

sql

/* Wrong: connected to the database being renamed */
\c mydb
ALTER DATABASE mydb RENAME TO newname;

/* Correct: connect to another database first */
\c postgres
ALTER DATABASE mydb RENAME TO newname;

📊

Quick Reference

Command	Description
ALTER DATABASE old_name RENAME TO new_name;	Rename a database from old_name to new_name.
\c database_name	Connect to a different database before renaming.
\l	List all databases to check names.

✅

Key Takeaways

Use ALTER DATABASE old_name RENAME TO new_name; to rename a database.

Connect to a different database before renaming; you cannot rename the one you are using.

Ensure you have the right privileges to rename the database.

Double-check database names to avoid errors.

Use \l to list databases and \c to switch connections in psql.