Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

ALTER SCHEMA

Updated on 2024-08-20 GMT+08:00

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;

  • 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

    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.

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

    NOTE:

    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.

  • default_charset

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

    This syntax is supported only when sql_compatibility is set to 'B'. For details about the supported character sets, see Table 1.

  • default_collate

    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.

    This syntax is supported only when sql_compatibility is set to 'B'. For details about the supported collation, see Table 1.

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

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback