SCHEMA
Tags: opengauss
Published: 2023-03-07 03:26:21

Schemas function as models. Schema management allows multiple users to use the same database without mutual impacts, to organize database objects as manageable logical groups, and to add third-party applications to the same schema without causing conflicts.

Each database has one or more schemas. Each schema contains tables and other types of objects. When a database is initially created, it has a public schema by default, and all users have the usage permission on the schema. Only the system administrator and initial users can create common functions, aggregate functions, stored procedures, and synonym objects in the public schema. Only the initial users can create operators in the public schema. Other users cannot create the preceding five types of objects even if they are granted the create permission. You can group database objects by schema. A schema is similar to an OS directory but cannot be nested.

The same database object name can be used in different schemas of the same database without causing conflicts. For example, both **a_schema** and **b_schema** can contain a table named **mytable**. Users with required permissions can access objects across multiple schemas of the same database.

When you execute the CREATE USER statement to create a user, the system creates a schema with the same name as the user in the database where the statement is executed.

Syntax

**·** Create a schema.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/txl9nbssqtp19xu23sdy.png)

**·** Modify a schema.

**·** Rename a schema.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zjowxm0g0tc277jx6mf8.png)

**·** Change the owner of a schema.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ijaq0yh88kl14ahc6dcv.png)

**·** Delete a schema and its objects.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y3k3v4l6em3pxvsik5rn.png)

Schemas beginning with **pg_temp** or **pg_toast_temp** are for internal use. Do not delete them. Otherwise, unexpected consequences may be incurred.

**Parameter Description**

**· schema_name**

Specifies the schema name.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pa88m1m9aw1jqaexqdio.png)

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

**· AUTHORIZATION user_name**

Specifies the owner of a schema. If **schema_name** is not specified, **user_name** will be used as the schema name. In this case, **user_name** can only be a role name.

Value range: an existing username or role name

**· schema_element**

Specifies an SQL statement defining an object to be created within a schema. Currently, only the **CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE PARTITION, CREATE SEQUENCE, CREATE TRIGGER** and **GRANT** clauses are supported.

Objects created by sub-commands are owned by the user specified by **AUTHORIZATION**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/isme1v9h1ys1p5y9kmbj.png)

**Examples**

**·** Create a schema for the **user1** user.

Create the **user1** user.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p0q6hech15s1ejfm3y5q.png)

Create a schema based on the username.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fbyzefyo9nc11f02w6et.png)

**·** Change the name of the **test** schema to **test1**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7vgof50462a5wybftg8e.png)

**·** Change the owner of the schema.

Create the **user2** user.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wzey9kmcnacpah49ktbx.png)

Change the owner of the **test1** schema to **user2**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tfay81srfx5sbeed683a.png)

**·** Check the current search path.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zgqvmtcjj9qvmngwke9h.png)

**·** Change the default schema of the current session.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c28p9wz7d761y7q74x96.png)

**·** Delete the schema and its objects.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8agc8dx3h9efo7nfqbwk.png)