PolarDB:Create a schema - PolarDB

CREATE SCHEMA creates a new schema owned by a specified user and populates it with tables, views, and grants—all in a single transaction. Either all objects are created successfully, or none of them are (including the schema itself).

Syntax

CREATE SCHEMA AUTHORIZATION username schema_element [ ... ];

Parameters

Parameter	Description
`username`	The user who will own the new schema. The schema takes the same name as the user. Only superusers can create schemas owned by other users.
`schema_element`	An SQL statement that defines an object to create within the schema. Accepted statements are `CREATE TABLE`, `CREATE VIEW`, and `GRANT`. To create other object types, run separate statements after the schema is created.

How it works

A schema is a namespace that contains named objects such as tables and views. Object names within a schema may duplicate those of objects in other schemas.

To access a named object, either:

Prefix the object name with the schema name (for example, myschema.mytable)
Set a search path that includes the desired schema

Unqualified object names are created in the current schema. Use the CURRENT_SCHEMA function to identify which schema is at the front of the search path.

Sub-statements within CREATE SCHEMA are treated essentially the same as separate statements issued after creating the schema. All created objects will be owned by the specified user.

Prerequisites

To create a schema, you must have the CREATE privilege on the current database.

Compatibility notes

Behavior	PolarDB for PostgreSQL (Compatible with Oracle)	Oracle
Role/username requirement	The role and username must already exist before running `CREATE SCHEMA`, but the schema itself must not exist yet.	The user (equivalently, the schema) must already exist.
`search_path` and `CURRENT_SCHEMA`	Supported	Not compatible with Oracle databases.

Examples

Create a schema with a table, a view, and a grant

The following example creates a schema named polardb, defines a table and a view within it, and grants SELECT access on the view to all users—in a single transaction.

CREATE SCHEMA AUTHORIZATION polardb
    CREATE TABLE empjobs (ename VARCHAR2(10), job VARCHAR2(9))
    CREATE VIEW managers AS SELECT ename FROM empjobs WHERE job = 'MANAGER'
    GRANT SELECT ON managers TO PUBLIC;