CREATE SCHEMA v16
Name
CREATE SCHEMA
— Define a new schema.
Synopsis
Description
This variation of the CREATE SCHEMA
command creates a schema owned by username
and populated with one or more objects. Creating the schema and objects occurs in a single transaction, so either all objects are created or none of them are, including the schema. If you're using an Oracle database, no new schema is created. username
, and therefore the schema, must exist before you use the command.
A schema is essentially a namespace. It contains named objects, such as tables and views, whose names might duplicate those of other objects existing in other schemas. You can access named objects either by qualifying their names with the schema name as a prefix or by setting a search path that includes the desired schemas. Unqualified objects are created in the current schema. The current scheme is the one at the front of the search path. Use the function CURRENT_SCHEMA
to determine the value.
Note
The search path concept and the CURRENT_SCHEMA
function aren't compatible with Oracle databases.
CREATE SCHEMA
includes subcommands to create objects in the schema. The subcommands are treated the same as separate commands issued after creating the schema. All the created objects are owned by the specified user.
Parameters
username
The name of the user to own the new schema. The schema also gets this name. Only superusers can create schemas owned by users other than themselves. In EDB Postgres Advanced Server, the role username
must already exist, but the schema must not exist. In Oracle, the user and schema must exist.
schema_element
A SQL statement defining an object to create in the schema. CREATE TABLE
, CREATE VIEW
, and GRANT
are accepted as clauses in CREATE SCHEMA
. You can create other kinds of objects in separate commands after you create the schema.
Notes
Nonsuperusers creating the schema must have the CREATE
privilege for the current database.
In EDB Postgres Advanced Server, other forms of the CREATE SCHEMA
command aren't compatible with Oracle databases.
Examples
export const _frontmatter = {"title":"CREATE SCHEMA","legacyRedirectsGenerated":["/edb-docs/d/edb-postgres-advanced-server/reference/database-compatibility-for-oracle-developers-reference-guide/9.6/Database_Compatibility_for_Oracle_Developers_Reference_Guide.1.047.html","/edb-docs/d/edb-postgres-advanced-server/user-guides/database-compatibility-for-oracle-developers-guide/9.5/Database_Compatibility_for_Oracle_Developers_Guide.1.075.html"],"redirects":["/epas/latest/epas_compat_sql/33_create_schema/"]}
- On this page
- Name
- Synopsis
- Description