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

• Modify the tamper-proof attribute of a schema.

  AlterSchema ::= ALTER SCHEMA schema_name { WITH | WITHOUT } BLOCKCHAIN

• Rename a schema.

  AlterSchema ::= ALTER SCHEMA schema_name
      RENAME TO new_name;

• Change the owner of a schema.

  AlterSchema ::= 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

  Rename a schema. If a non-administrator user wants to change the schema name, the user must have the CREATE permission on the database.

  new_name: new name of the schema.

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

• OWNER TO new_owner

  Change 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.

• { WITH | WITHOUT } BLOCKCHAIN

  Modifies the tamper-proof attribute of a schema. Common row-store tables with the tamper-proof attribute are tamper-proof history tables, excluding foreign tables, temporary tables, and system catalogs. The tamper-proof attribute can be modified only when no table is contained in the schema. In addition, the temporary table mode is not supported. Modify the tamper-proof attribute in toast table mode, dbe_perf mode, and blockchain mode.

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 'xxxxxxxxx';

-- 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;

Helpful Links

CREATE SCHEMA，DROP SCHEMA