ALTER DATABASE

Description

In the M-compatible database, DATABASE and SCHEMA are synonyms that allow you to modify the character set, collation, and owner of a database or schema.

Precautions

Only the owner of a schema or a user granted the ALTER permission for a schema has the permission to run the ALTER DATABASE command. When separation of duties is disabled, a 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 also 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

Modify the database owner.

ALTER DATABASE database_name 
    OWNER TO new_owner;

Modify the character set and collation of the database.

ALTER {DATABASE | SCHEMA} database_name
    [create_option] ...
create_option: [DEFAULT] {
    CHARACTER SET [=] default_charset
  | CHAR SET [=] default_charset
  | CHARSET [=] default_charset
  | COLLATE [=] default_collation
}

Parameters

database_name
Specifies the name of the database whose attributes are to be modified.

Value range: a string complying with Identifier Description.
new_owner
Specifies the new owner of a database.

Value range: a string indicating a valid username. For details about the username requirements, see •user_name.
COLLATE [=] default_collation
This parameter is optional and specifies the character set used by the database. For example, set this parameter by using collate = 'zh_CN.gbk'.

The use of this parameter affects the sort order of strings (for example, the order of using ORDER BY for execution and the order of using indexes on text columns). By default, the sorting order of the template database is used.

Value range: See Database-level Character Sets and Collations.
{CHAR SET | CHARSET | CHARACTER SET} [=] default_charset
This parameter is optional and specifies the character class used by the database. For example, set this parameter by using CHARSET = 'zh_CN.gbk'. The use of this parameter affects the classification of characters, such as uppercase letters, lowercase letters, and digits. By default, the character classification of the template database is used.

Value range: See Database-level Character Sets and Collations.

Examples

Modify the character set and collation of the database.

-- Create a database test_db1.
m_db=# CREATE DATABASE test_db1;
-- Change the character set of test_db1 to utf8.
m_db=# ALTER DATABASE test_db1 CHARSET utf8;
-- Change the collation of test_db1 to utf8mb4_bin.
m_db=# ALTER DATABASE test_db1 COLLATE utf8mb4_bin;

Modify the database owner.

-- Create user scott.
m_db=# CREATE USER scott PASSWORD '********';
-- Change the owner of test_db1 to scott.
m_db=# ALTER DATABASE test_db1 OWNER TO scott;