PostgreSQL offers a bundle of statements to work with the schemas. These statements may include “CREATE”, “DROP”, and “ALTER”. The “ALTER SCHEMA” statement is used to modify the schema’s definition, such as altering the schema’s owner, renaming a schema, and so on.

The objective of this post is to explain all the possible methods to rename a schema in PostgreSQL. In this write-up, we will demonstrate how to rename a schema in Postgres using the following methods:

• Method 1: How to Rename a Schema Using SQL Shell?
• Method 2: How to Rename a Schema Using pgAdmin?

Let's start with the first method.

Method 1: How to Rename a Schema Using SQL Shell?

The SQL Shell supports the command line interface on which the user can run and execute the statements. For instance, the “ALTER SCHEMA” statement can be used with the RENAME TO clause to rename a particular schema in PostgreSQL:

ALTER SCHEMA schema_name 
RENAME TO new_name;

In the above syntax, “schema_name” identifies the name of the existing schema in the database. While the “new_name” refers to the altered/modified name of the schema.

Step #1: List Schemas

Firstly, open the SQL Shell, provide the required details and execute the “\dn” command to see the list of available schemas:

\dn;

The output shows that there are three schemas in the “postgres” database.

Step #2: Rename Schema

Suppose we want to rename the “employee_info” schema from emp_info to employee_details. To do that, we will execute the “ALTER SCHEMA” statement with the collaboration of the “RENAME TO” clause as follows:

ALTER SCHEMA employee_info 
RENAME TO employee_details;

The output indicates that the employee_info schema has been altered successfully.

Step #3: Verify Schema

Let’s execute the “\dn” command one more time to verify that if the selected schema has been renamed or not:

\dn;

The output proves that the employee_info schema has been renamed to employee_details.

Method 2: How to Rename a Schema Using pgAdmin?

You can execute the same ALTER SCHEMA command with the collaboration of RENAME TO statement in the pgAdmin’s query tool to rename a particular schema. To open the Query Tool, right click on the targeted schema and choose the “Query Tool” option from the dropdown list:

Clinking on the Query Tool will open the desired tool where you can execute any query of your choice.

Step #2: Rename Schema

Let’s execute the “ALTER SCHEMA” statement with the help of “RENAME TO” statement to rename the “example” schema to “exp_schema”:

ALTER SCHEMA example
RENAME TO exp_schema;

The output shows that the selected schema has been altered successfully.

Step #3: Verify Schema

On refreshing the Schemas tab, the users can verify that a schema named “exp_schema” is located on the left side of the window:

The output verifies that the example schema has been renamed to exp_schema. This is how you can rename a particular schema through SQL SHELL or pgAdmin.

Conclusion

In PostgreSQL, the “ALTER SCHEMA” statement is used with the collaboration of the “RENAME TO” statement to rename a particular schema. This post explained how to rename a particular schema using SQL SHELL(psql) or pgAdmin.