ALTER SCHEMA

Function

ALTER SCHEMA alters the attributes of a schema.

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 modify a schema owner, you must be the schema owner or 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 schemas of the system may make some functions unavailable or even affect the normal running of the database. By default, the names of the built-in schemas of the system cannot be changed. To ensure forward compatibility, you can change the names of the built-in schemas only when the system is being started or upgraded or when allow_system_table_mods is set to on.

Syntax

• Modify the tamper-proof attribute of a schema.

  1	ALTER SCHEMA schema_name { WITH | WITHOUT } BLOCKCHAIN

• Rename a schema.

  1 2	ALTER SCHEMA schema_name RENAME TO new_name;

• Change the owner of a schema.

  1 2	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.

  new_name: new name of the schema.

  Value range: a string. It must comply with the identifier 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 owning 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.

• { WITH | WITHOUT } BLOCKCHAIN

  Modify 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. The tamper-proof attribute of the toast table schema, dbe_perf schema, and blockchain schema cannot be modified. This syntax can be used to convert between normal and tamper-proof modes only if the schema does not contain any tables.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

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

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

-- Modify the tamper-proof attribute of ds_new.
openGauss=# ALTER SCHEMA ds_new WITH BLOCKCHAIN;

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

Helpful Links

CREATE SCHEMA and DROP SCHEMA