ALTER SCHEMA

Function

ALTER SCHEMA modifies schema properties.

Precautions

Only the schema owner or a user granted with the ALTER permission can run the ALTER SCHEMA command. The system administrator has this permission by default. To modify a schema owner, you must be the schema owner or system administrator and a member of the new owner role.

Syntax

• Rename a schema.

  ALTER SCHEMA schema_name
      RENAME TO new_name;

• Change the owner of a schema.

  ALTER SCHEMA schema_name
      OWNER TO new_owner;

Parameter Description

• schema_name

  Specifies the name of an existing schema.

  Value range: an existing schema name

• RENAME TO new_name

  Renames a schema.

  new_name: new name of the schema. To do this as a non-administrator, you must have CREATE permission in the database.

  Value range: a string. It must comply with the naming convention rule.

• OWNER TO new_owner

  Changes the owner of a schema. To do this as a non-administrator, you must be a direct or indirect member of the new owning role, and that role must have CREATE permission in the database.

  new_owner: new owner of the schema.

  Value range: an existing username or role name

Examples

-- Create the ds schema.
mogdb=# CREATE SCHEMA ds;

-- Rename the current schema ds to ds_new.
mogdb=# ALTER SCHEMA ds RENAME TO ds_new;

-- Create user jack.
mogdb=# CREATE USER jack PASSWORD 'Bigdata@123';

-- Change the owner of ds_new to jack.
mogdb=# ALTER SCHEMA ds_new OWNER TO jack;

-- Delete user jack and schema ds_new.
mogdb=# DROP SCHEMA ds_new;
mogdb=# DROP USER jack;