Help Center/ GaussDB/ Developer Guide(Centralized_3.x)/ SQL Reference/ SQL Syntax/ ALTER SCHEMA
Updated on 2024-05-07 GMT+08:00
View PDF
Share

ALTER SCHEMA

Description

Modifies schema attributes.

Precautions

  • Only the owner of a schema or users granted with the ALTER permission on the schema can run the ALTER SCHEMA command. The system administrator has this permission by default. To change the owner of a schema, you must be the owner of the schema or system administrator and a member of the new owner role.
  • For system schemas other than public, such as pg_catalog and sys, only the initial user is allowed to change the owner of a 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.
  • Only the initial user can change the owner of a schema to an O&M administrator. Other users cannot change the owner of a schema to an O&M administrator.

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;
  • Modify the default character set and collation of the schema. 
    ALTER SCHEMA schema_name 
    [ [DEFAULT] CHARACTER SET | CHARSET [ = ] default_charset ] [ [DEFAULT] COLLATE [ = ] default_collation ];

Parameters

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

    • The schema name must be unique in the current database.
    • The schema name cannot be the same as the initial username of the current database.
    • The schema name cannot start with pg_.
    • The schema name cannot start with gs_role_.

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

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

  • default_charset

    This syntax is supported only when sql_compatibility is set to 'B'. This command is used to modify the default character set of a schema. If you specify a schema separately, the default collation of the schema is set to the default collation of the specified character set.

  • default_collate

    This syntax is supported only when sql_compatibility is set to 'B'. This command is used to change the default collation of a schema. If you specify a collation separately, the default character set of the schema is set to the character set corresponding to the specified collation.

    For details about the supported collation, see Table 1.

Examples

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

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

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

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

-- Change the default character set of DS_NEW to utf8mb4 and the default collation to utf8mb4_bin.
gaussdb=# ALTER SCHEMA ds_new CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

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

Helpful Links

CREATE SCHEMA and DROP SCHEMA

Parent topic: SQL Syntax