ALTER SCHEMA

Description

Alters schema attributes.

Precautions

Only the owner of a schema or users granted the ALTER permission on the schema can run the ALTER SCHEMA command. System administrators have this permission by default. To change the owner of a schema, you must be the owner of the schema or a system administrator and a member of the new owner role.
Only the initial user is allowed to change the owner of the pg_catalog system schema. Changing the names of the built-in system schemas may make some functions unavailable or even affect the normal running of the database. By default, the names of the built-in system schemas cannot be changed. To ensure forward compatibility, you can change the names of the built-in system schemas only when the system is being started or upgraded or when allow_system_table_mods is set to on.

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;

Parameters

schema_name
Specifies the name of an existing schema.

Value range: an existing schema name.
RENAME TO new_name
Renames a schema. If a non-SYSADMIN 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 that complies with the Identifier Naming Conventions.
OWNER TO new_owner
Changes the owner of a schema. To do this as a non-system administrator, you must be a direct or indirect member of the new owner role, and that role must have the CREATE permission on the database.

new_owner: new owner of the schema.

Value range: an existing username or role name.

Examples

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

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

-- Create user jack.
openGauss=# CREATE USER jack PASSWORD '********';

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

-- Drop user jack and schema ds_new.
openGauss=# DROP SCHEMA ds_new;
openGauss=# DROP USER jack;