In this section, we are going to learn PostgreSQL ALTER SCHEMA command, which is used to change the description of a schema.
ALTER SCHEMA command
The alter schema command will allow us to modify the schema’s definition. For example, we can also rename a schema with the help of the below alter schema command.
The syntax of altering schema command is as follows:
ALTER SCHEMA schema_name
RENAME TO new_name;We have the following parameters which are used in the above syntax:
| Parameters | Description |
|---|---|
| Alter Schema | It is a keyword which is used to change the description of a schema |
| Schema_Name | It is used to describe the name of the schema, which we need to rename it. |
| New_Name | This parameter is used to define the new name of the schema, and we cannot start the new name with pg_, because in PostgreSQL, such names are kept for system schemas. |
Note: For executing the above command, we must be the owner of the schema, and we also have access to CREATE privilege for the database.
Apart from renaming a schema, in the ALTER SCHEMA, we can also modify the owner of a schema for the new one with the below command’s help:
ALTER SCHEMA schema_name
OWNER TO { new_owner | CURRENT_USER | SESSION_USER};We have the following parameters which are used in the above syntax:
| Parameters | Description |
|---|---|
| Schema_Name | For modifying the owner in the ALTER SCHEMA condition, we will describe the name of the schema. |
| New_owner | It is used to define the new owner in the OWNER TO condition. |
Examples of PostgreSQL ALTER SCHEMA command
Let us see few examples where we can understand how to use the ALTER SCHEMA command in PostgreSQL. These below examples are based on the schema which we created in the PostgreSQL CREATE SCHEMA section.
To rename a schema using ALTER SCHEMA command
This below example is explained to rename a schema with the help alter schema command.
Here, we are going to rename the myschema schema to Schema1 schema:
ALTER SCHEMA myschema
RENAME TO Schema1;Output
Once we execute the above command, we will get the below Output window:
In the same way, the following example is used to describe the renames for the Company schema to the department:
ALTER SCHEMA Company
RENAME TO department; Output
After executing the above command, we will get the below output:
To modify the owner of a schema using alter schema command
In this particular example, we will modify the owner of the Schema1 from myschema to Postgres with the help of Alter schema:
ALTER SCHEMA Schema1
OWNER TO postgres; 
And then, we will execute the user-created schema command:
SELECT * FROM pg_catalog.pg_namespace
WHERE nspname NOT LIKE 'pg_%' AND
nspacl is NULL
ORDER BY nspname; Note: In PostgreSQL, the pg_catalog is the typical metadata and core schema used by Postgres to internally accomplish the things.
The pg_namespace is a catalog, which is used to store the namespaces. And a namespace is the structure of fundamental SQL schemas, and each namespace can have a distinct collection of types, relations, etc. without any name conflicts.
Output
After executing the above query, we will get the below output, which contains one table, and we can see that the Schema1 schema is maintained by the owner id 10, which is Postgres id.
Leave a Reply