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. When separation of duties is disabled, system administrators have 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.
• Except the initial user, other users cannot change the owner of a schema to an O&M administrator.

Syntax

• Alter the tamper-proof attribute of a schema.

  1	ALTER SCHEMA schema_name { WITH | WITHOUT } BLOCKCHAIN;

  

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

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

• { WITH | WITHOUT } BLOCKCHAIN

  Alters the tamper-proof attribute of a schema by using WITH. 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 altered only when no table is contained in the schema. The tamper-proof attribute of the temporary table schema, 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.

  

  To change a common schema to a tamper-proof schema, set the GUC parameter enable_ledger to on. The default value is off, and the level is SIGHUP.

Examples

• Alter the tamper-proof attribute of a schema.
  To alter the common mode to the tamper-proof mode, you need to set the GUC parameter enable_ledger to determine whether to enable the ledger database function. Contact the administrator for information about how to use parameters.

  -- Create a schema test_schema1.
  gaussdb=# CREATE SCHEMA test_schema1;
  
  -- Change the schema test_schema1 to the tamper-proof mode.
  gaussdb=# ALTER SCHEMA test_schema1 WITH BLOCKCHAIN;
  
  -- Query mode information. The tamper-proof attribute is true.
  gaussdb=# \dn+ test_schema1
                               List of schemas
       Name     | Owner | Access privileges | Description | WithBlockChain 
  --------------+-------+-------------------+-------------+----------------
   test_schema1 | omm   |                   |             | t
  (1 row)

• Rename a schema.

  -- Rename the schema test_schema1 to test_sch1.
  gaussdb=# ALTER SCHEMA test_schema1 RENAME TO test_sch1;
  
  -- Query schema information.
  gaussdb=# \dn+ test*
                             List of schemas
     Name    | Owner | Access privileges | Description | WithBlockChain 
  -----------+-------+-------------------+-------------+----------------
   test_sch1 | omm   |                   |             | t
  (1 row)

• Change the owner of a schema.

  -- Create user test_user.
  gaussdb=# CREATE ROLE test_user PASSWORD '********';
  
  -- Change the owner of schema test_sch1 to test_user.
  gaussdb=# ALTER SCHEMA test_sch1 OWNER TO test_user;
  
  -- Query schema information.
  gaussdb=# \dn+  test_sch1;
                               List of schemas
     Name    |   Owner   | Access privileges | Description | WithBlockChain 
  -----------+-----------+-------------------+-------------+----------------
   test_sch1 | test_user |                   |             | t
  (1 row)
  
  -- Delete.
  gaussdb=# DROP SCHEMA test_sch1;
  gaussdb=# DROP ROLE test_user;

• Modify the default character set and collation.

  This syntax is supported only when sql_compatibility is set to 'B'.

  Except the binary character set and collation, only the character set that is the same as the database encoding can be specified.

  -- Create and switch to the test database.
  gaussdb=# CREATE DATABASE test1 WITH DBCOMPATIBILITY = 'B' ENCODING = 'UTF8' LC_COLLATE = 'zh_CN.utf8' LC_CTYPE = 'zh_CN.utf8';
  
  gaussdb=# \c test1
  
  -- Create a schema test_sch2.
  test1=# CREATE SCHEMA test_sch2;
  
  -- Change the default character set to utf8mb4 and the default collation of characters to utf8mb4_bin.
  test1=# ALTER SCHEMA test_sch2 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
  
  -- Delete.
  test1=# DROP SCHEMA test_sch2;
  
  -- Switch to the default database. Change the database name based on actual situation.
  test1=# \c postgres
  gaussdb=# DROP DATABASE test1;

Helpful Links

CREATE SCHEMA and DROP SCHEMA