# PostgreSQL ALTER SCHEMA

> PostgreSQL ALTER SCHEMA: an educational article from Command Prompt

PostgreSQL offers an “ **ALTER SCHEMA** ” statement that is used to modify the schema’s definition, such as altering the schema’s owner, renaming a schema, and so on. In order to modify the schema’s definition, you must own that schema.

This write-up will teach you how to change the schema’s owner and how to rename a schema in PostgreSQL using the **ALTER SCHEMA** statement. So, let’s start.

 **How to Change Schema’s Owner Using ALTER SCHEMA Statement in Postgres?**

In PostgreSQL, the ALTER SCHEMA statement is used with the collaboration of the OWNER TO clause to change the schema’s owner. To do so, follow the below syntax:
    
    
    ALTER SCHEMA schema_name
    OWNER TO new_owner;

In the above syntax, the “ **schema_name** ” represents the schema to be altered while “ **new_owner** ” represents the name of the new owner.

The below-listed steps will help you understand the working of ALTER SCHEMA statement:

 **Step 1: Launch SQL Shell**

Firstly, open the SQL Shell(psql) and provide the necessary details like user name, password, etc.:

Hit the “enter” button to proceed further.

 **Step 2: Show Available Schemas**

Run the “\dn” command to get the list of available schemas:
    
    
    \dn;

Output shows the schema's names and their owners. Alternatively, you can run the below command to check the schema’s owner:
    
    
    SELECT * FROM pg_catalog.pg_namespace;

 **Step 3: Show Available Users**

Let’s run the “\du” command to see the available users:
    
    
    \du

 **Step 4: Change Owner**

Suppose we want to change the owner of the “ **employee_details1** ” schema from “ **postgres** ” to “ **command_prompt** ”. To do so, we will execute the following command:
    
    
    ALTER SCHEMA employee_details1 OWNER TO command_prompt;

The “ **ALTER SCHEMA** ” message in the output proves that the selected schema has been altered successfully.

 **Step 5: Verify The Owner**

Let’s run the “\dn” command to verify the working of “ **ALTER TABLE** ” and “ **OWNER TO** ” statements:
    
    
    \dn

The output shows that the schema owner has been successfully changed from “ **postgres** ” to “ **command_prompt** ”.

Alternatively, you can verify the schema’s owner via the following command:
    
    
    SELECT * FROM pg_catalog.pg_namespace;

The output authenticates the working of the **ALTER SCHEMA** statement.

 **How to Rename a Schema Using ALTER SCHEMA Statement in Postgres?**

In PostgreSQL, the **ALTER SCHEMA** statement is used along with the **RENAME TO** clause to modify the schema’s name. To do so, follow the below syntax:
    
    
    ALTER SCHEMA schema_name 
    RENAME TO new_name;

The “ **schema_name** ” is the schema to be altered while “ **new_name** ” represents the new/modified name of the schema.

 **Step 1: Check Available Schemas**

The below command will show the list of available schemas:
    
    
    \dn;

Let’s rename the “ **employee_details1** ” schema.

 **Step 2: Rename Schema**

Let’s use the following command to rename the “ **employee_details1** ” schema to “ **emp_info** ”:
    
    
    ALTER SCHEMA employee_details1 RENAME TO emp_info;

The “ALTER SCHEMA” message proves that the targeted schema has been altered successfully.

 **Step 3: Verify Schema Name**

Let’s run the below command to verify the modified schema’s name:
    
    
    \dn;

The output verified that the selected schema had been renamed successfully.

 **Conclusion**

PostgreSQL offers an “ **ALTER SCHEMA** ” statement that is used to modify the schema’s definition, such as altering the schema’s owner, renaming a schema, and so on. Use the **ALTER SCHEMA** statement with the collaboration of the **OWNER TO** clause to change the schema’s owner. Use **ALTER SCHEMA** statement along with the **RENAME TO** clause to rename the schema’s name. This article explained the working of **ALTER SCHEMA** statements using practical examples.

---
[View this page online](https://www.commandprompt.com/education/postgresql-alter-schema/)